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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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!
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,218,560
Messages
6,143,202
Members
450,469
Latest member
brent3162

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