Solved

SUMPRODUCT FORMULA IN EXCEL

Posted on 2009-07-08
8
426 Views
Last Modified: 2012-05-07
Hello.  I'm using a SUMPRODUCT formula as follows:  =SUMPRODUCT(('wisconsin 006 036 11-1-08 to 4-'!$H$9:$H$11="036")*('wisconsin 006 036 11-1-08 to 4-'!$I$9:$I$11>=1)*('wisconsin 006 036 11-1-08 to 4-'!$I$9:$I$11<=1499)*('wisconsin 006 036 11-1-08 to 4-'!$M$9:$M$11>0)*('wisconsin 006 036 11-1-08 to 4-'!$N$9:$N$11>0)).

I put quotation marks around the 036 in $H$9:$H$11="036" because if I don't put quotation marks around 036,  Excel changes it to 36.  Excel seems to be accepting it as a correct formula.  Is this okay and will it work?

Thank you!
0
Comment
Question by:bifsniff
  • 3
  • 3
8 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 24807925
It will work in that it will count values in that range that are text-formatted as "036". Without seeing your data I can't tell if that's right for you here.
One option would be to "coerce" the range $H$9:$H$11 to numeric and just use 36, i.e.
=SUMPRODUCT(('wisconsin 006 036 11-1-08 to 4-'!$H$9:$H$11+0=36)*('wisconsin 006 036 11-1-08 to 4-'!$I$9:$I$11>=1)*('wisconsin 006 036 11-1-08 to 4-'!$I$9:$I$11<=1499)*('wisconsin 006 036 11-1-08 to 4-'!$M$9:$M$11>0)*('wisconsin 006 036 11-1-08 to 4-'!$N$9:$N$11>0))
that will fail, however, if H9:H11 contains any text values that can't be coerced to numbers.....
regards, barry
0
 

Author Comment

by:bifsniff
ID: 24808021
Thanks Barry.  I went in and formatted all the cells in column H as Text.  I hope it works.

Thanks so much for your help!
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 24808070
If you have existing numbers in the range and format as text at that point then I don't believe that will actually change the values to text.
....a better approach than the first one I suggested, try this formula
=SUMPRODUCT((TEXT('wisconsin 006 036 11-1-08 to 4-'!$H$9:$H$11,"000")="036")*('wisconsin 006 036 11-1-08 to 4-'!$I$9:$I$11>=1)*('wisconsin 006 036 11-1-08 to 4-'!$I$9:$I$11<=1499)*('wisconsin 006 036 11-1-08 to 4-'!$M$9:$M$11>0)*('wisconsin 006 036 11-1-08 to 4-'!$N$9:$N$11>0))
regards, barry
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:bifsniff
ID: 24808115
Wow, I could have never figured that out in a million years.  Sure will.  Thanks!
0
 

Author Comment

by:bifsniff
ID: 24809106
It seems to be working as far as I can tell Barry.  Thanks so much!
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 24809530
If you're happy with the proposed solution(s) bifsniff then you should just accept one or more solutions rather than request closure.....
reagrds, barry
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question