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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=557152

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 wrote:

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

--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=557152

--

Dave Peterson

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
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=557152

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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=557152

See if perhaps this might help you understand it even mo

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Gazzr" wrote in message
...

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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile:
http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=557152