Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SUMPRODUCT FORMULA IN EXCEL

Posted on 2009-07-08
8
Medium Priority
?
476 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

618 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