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

[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

--
HTH,

RD

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

"Gazzr" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>