SUMPRODUCT Works Sometimes Why

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have use this formula before, but i try it again on another sheet and now
it does not work.

This Works:
=SUMPRODUCT(('Dec Actual'!$F$2:$F$100="0103")*('Dec
Actual'!$G$2:$G$100=B101)*('Dec Actual'!$I$2:$I$100))

This does not:
=SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B350=A8)*('Source'!AM5:AM350))
 
=SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B350=A8)*('Source'!AM5:AM350))

would return #VALUE! when you have text (including formula blanks) in
'Source'!AM5:AM350. Switch to the comma syntax...

=SUMPRODUCT(--('Source'!A5:A350="A"),--(Source'!B5:B350=A8),'Source'!AM5:AM350)

=SUMPRODUCT(('Source'!A5:A350="A")+0,(Source'!B5:B350=A8)+0,'Source'!AM5:AM350)

=SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B350=A8),'Source'!AM5:AM350)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct - Return value as Blank 3
Sumproduct Excluding Data 2
Excel Countifs/Sumproduct with mutil Or statement 3
Sumproduct formula help needed 6
Scrabble Value calculation for Welsh words 0
Wildcard not working in SumProduct Array 8
sumproduct with multiple criteria in single column 4
Sumproduct to count between two dates 2

Back
Top