Sumproduct - first instance only?

rob092

New Member
Joined
Mar 18, 2004
Messages
48
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,368
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

New Member
Joined
Mar 18, 2004
Messages
48
Actually its because I am summing the wrong column.....sigh :)

Sorry to bother.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,444
Members
410,610
Latest member
renatha prado
Top