SUMPRODUCT worked...now it doesn't work

phil007

New Member
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.

Sheet1

Sheet2

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.
Try just =B2 [ no &"" ] .

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.

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)

Thanks to everyone for the very quick responses...that fixed it! I'll also start using SUMIF.

Replies
3
Views
203
Replies
7
Views
735
Replies
17
Views
441
Replies
20
Views
862
Replies
2
Views
293

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.

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

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