?
Solved

SUMPRODUCT FORMULA IN EXCEL

Posted on 2009-07-08
8
Medium Priority
?
468 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 1000 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 1000 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 IT Pros

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

741 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