Google sheets/Drive- SUMIFS excluding a cell with no value

Question

I'm trying to make a formula in Excel Drive were the amount of the invoice is divided by the amount of kilometres that a trip has made. In column H I have all the invoiced amount per trip. In column E I have all the kilometres for that trip. I want all the information in column H divided by the information in column E. I only want the kilometres added in the formula, when the invoiced amount is filled. I made the following formula, but due to my lack of knowledge it doesn't work.

=SUMIFS(Sheet1!$H:$H;Sheet1!$B:$B;2021)/SUMIFS(Sheet!$E:$E;Sheet!$B:$B;2021;Sheet!$H:$H;<>0)

Can anyone help me out or am I asking the impossible?

Answer 1

I see multiple issues with your formula,

Try this instead:

=SUMIFS(Sheet1!$H:$H;Sheet1!$B:$B;"2021")/SUMIFS(Sheet1!$E:$E;Sheet1!$B:$B;"2021";Sheet1!$H:$H;"<>0")

Issues:

• 2nd SUMIFS have Sheet instead of Sheet1 (I assume you are looking into the same sheet which is Sheet1)
• surround the criterion with double quotes especially if it has a comparison operator such as >, <, <>, =. 2021 will do without double quotes but for best practice, surround it too.

Output: