Sumproduct - first instance only?

[rob092]

I haven't seen this addressed anywhere else in the forum history, so I'll post this here.

=SUMPRODUCT(--($C$2:$C$1300=$AA53),--($X$2:$X$1300="c"),G$2:G$1300)

AA53 is a reference to the text of a first criteria (it changes on each row, c is the column where this criteria can be found

"c" is my second criteria which can be found in the x column

g is the column I am trying to sum as a result of both criteria

Now it works, but it is only summing the first instance it finds and not every instance of the criteria I am looking for.

Is there a get around to this limitation or am I doing something wrong?

edit: it is working for me on the orginal sheet i created using it, but not this one....strange.

Thanks!

-Rob

 

[Domenic]

Maybe the numbers in Column G are being recognized as text. Try coercing them into numerical values...

1) Select an empty cell

2) Edit > Copy

3) Select the range of numbers in Column G

4) Edit > Paste Special > Add > Ok

Hope this helps!

 

[Brian from Maui]

If column G is text formatted, try switching to a * syntax instead of the , syntax.

=SUMPRODUCT(($C$2:$C$1300=$AA53)*($X$2:$X$1300="c")*(G$2:G$1300))

 

[rob092]

Actually its because I am summing the wrong column.....sigh

Sorry to bother.