Help with SUMPRODUCT?

[Gazzr]

Hi All,

I have to say I'm pulling what little hair I have out over my
SUMPRODUCT formula.

Can anyone advise what I am doing wrong with the formula below?
Basically I am trying to Sum column AA based on two conditions in Col H
and Col S.

When I enter the formula i get the #NUM! error?

=SUMPRODUCT(--(Sheet1!H:H="LN"),--(Sheet1!S:S="Garry"),Sheet1!AA:AA)

Thanks everyone.
Garry

 

[Dave Peterson]

You can't use the whole column for this kind of formula.

Pick a row that is big enough and use that:

=SUMPRODUCT(--(Sheet1!H1:H9999="LN"),--(Sheet1!S1:S9999="Garry"),
Sheet1!AA1:AA9999)

 

[Gazzr]

Hi Dave,

Thanks for the help! I have fixed up the cell ranges as suggested and I
now get 0 as the result (Not as bad as #NUM!). I have made sure that the
cell ranges are correct and that the conditions that I am checking
against actually exist in Sheet1 but to no avail.

I will do some more testing and report back.

Thanks mate
Garry

 

[Gazzr]

Hi Dave,

User error I'm afraid, cell references were wrong, I was looking at Col
A, not Col AA.

Very happy that I know how to use SUMPRODUCT though!

Thanks again
Garry

 

[Ragdyer]

See if perhaps this might help you understand it even more:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html