Solved

SUMPRODUCT FORMULA IN EXCEL

Posted on 2009-07-08
8
418 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now