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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Domenic

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

Forum statistics

Threads
1,141,011
Messages
5,703,724
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top