Solved

SUMPRODUCT FORMULA IN EXCEL

Posted on 2009-07-08
8
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

691 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