SUM.IF multiple columns when first column is 0

[Solari]

Hi!

I have a working SUM.IF that looks at a range of 3 columns and returns a single columns sum if the combined sum is larger than 0.
The formula only works if there is numbers in the first column!

Example:
=IF(SUM.IF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$K:$M)>0;SUM.IF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$L:$L);Other_sheet!J15)
L3 contain the month as does column H in Data_sheet. That is not the problem.

Data_sheet contains the numbers, but in this case only columns L and M has numbers, K is only zeroes.
If I manually input a single 1 in column K the formula works for L and M...

What am I doing wrong?

 

[jasonb75]

Because the criteria range is only 1 column ($H:$H) only the first column of the sum range (column K) is evaluated. For sumif to work correctly the sum and criteria range must be equal in size.

You would either need to use a sumproduct formula, or sumif on each individual column then add them together.

=IF((SUMIF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$K:$K)+SUMIF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$L:$L)+SUMIF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$M:$M))>0;SUMIF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$K:$K)+SUMIF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$L:$L)+SUMIF(Data_sheet!$H:$H;Sum_sheet!L$3;Data_sheet!$M:$M);Other_sheet!J15)

 

[Solari]

Thank you Jason!

Such a simple answer but still hadn't hit me