Basic SUMPRODUCT not working

[Folander]

Hi all,

I am having trouble with a basic sumproduct formula, it's a two criteria sumif, but I need it to be a sumproduct so it can work in a closed workbook. Can someone help work out why it won't work please? The formula does work when I remove either of the criteria but doesn't work with both.

=SUMPRODUCT((C4:C9=C4)*(C4:C9=C9),F4:F9)

Any help would be much appreciated.

 

[jasonb75]

Looks like one of the ranges might be wrong, what exactly are you expecting the formula to look at?

 

[Folander]

Looks like one of the ranges might be wrong, what exactly are you expecting the formula to look at?

I just want to add up salaries and bonus. Once I get it working I will add more criteria but I'm stumped why it won't work.

 

[RoryA]

Your current version requires each row in column C to match both C4 and C9 at the same time, which can't work unless C4 and C9 contain the same value (in which case the double test is pointless anyway). You probably need to add not multiply your criteria to get an OR:

=SUMPRODUCT((C4:C9=C4)+(C4:C9=C9),F4:F9)

Though based on your picture you probably should be using column D not C.

 

[jasonb75]

If you want to add together 2 different criteria in the same column then you need to use an OR syntax, not an AND syntax which you are trying to use. That method is matching criteria in the same row but different columns.

=SUMPRODUCT(((C4:C9=C4)+(C4:C9=C9)),F4:F9)

 

[Folander]

Many thanks RoryA and Jasonb75, I understand where I went wrong now. Problem solved!

 

[bebo021999]

Which column is C and which is F?
Assume "C" is code (E2000,...), F is value

Your formula is using *, means "both" not "either". Should be "+"
=SUMPRODUCT((C4:C9=C4)*(C4:C9=C9),F4:F9)

Also you may need NOT to use relative address

Try:

=SUMPRODUCT((($C$4:$C$9=$C$4)+($C$4:$C$9=$C$9))*$F$4:$F$9)