• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Sumproduct

The below formula is returning a number but it should be 0.  Please see attached test file.  The cell in yellow is the one I'm having problems with.  It seems to be counting blank cells as 0.

=SUMPRODUCT((Responses!$A$2:$A$1000=$B7)*(Responses!$B$2:$B$1000=L$4))

Any help would be greatly appreciated!

Thanks.
test.xls
0
dddw
Asked:
dddw
  • 2
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
The easiest way it to enter the zero as a text (precede it with an apostrophe).


This modified formula may do the trick

=SUMPRODUCT((Responses!$A$2:$A$1000=$B7)*(Responses!$B$2:$B$1000=L$4)*(LEN(Responses!$B$2:$B$1000)>0))

0
 
barry houdiniCommented:
Which version of Excel are you using? If you have Excel 2007 or later try COUNTIFS

=COUNTIFS(Responses!$A$2:$A$1000,$B7,Responses!$B$2:$B$1000,L$4)

regards, barry
0
 
dddwAuthor Commented:
I'm using 2003.  
0
 
barry houdiniCommented:
OK, in that case I recommend Saqib's approach, add another check to the SUMPRODUCT formula

regards, barry
0
 
dddwAuthor Commented:
Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now