SUMPRODUCT worked...now it doesn't work

phil007

New Member
Joined
Feb 10, 2004
Messages
33
Last month I used SUMPRODUCT to complete a large report. This month I'm only getting a row of zeros. I'm guessing it is some type of format error but I've spent half a day on this and can't get it to work. :(

Thanks...Phil

The formula used in Sheet1 column C is =SUMPRODUCT(--(Sheet2!$A$1:$A$5000=B2&""),Sheet2!$B$1:$B$5000). Sheet1, column C will show the sum of $ in Sheet2 column B when for that account.

Download Example

Sheet1
sheet1.gif


Sheet2
sheet2.gif
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi,

Change formula to

=SUMPRODUCT(--(Sheet2!$A$1:$A$5000=B2),Sheet2!$B$1:$B$5000)

as you have numbers.

Or better, use SUMIF() as you have only one condition.
 
Upvote 0
If A1 on Shee2 is not text, the

B2&""

bit is the reason why you get 0's.

But, why don't you use a formula with SumIf...

=SUMIF(Sheet2!$A$1:$A$5000,B2,Sheet2!$B$1:$B$5000)
 
Upvote 0
Thanks to everyone for the very quick responses...that fixed it! I'll also start using SUMIF.
 
Upvote 0

Forum statistics

Threads
1,207,423
Messages
6,078,440
Members
446,338
Latest member
AliB

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