+ Reply to Thread
Results 1 to 14 of 14

Sumproduct with TRUE cell entries

  1. #1
    Registered User
    Join Date
    07-07-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Red face Sumproduct with TRUE cell entries

    I am trying to count all instances where A2:A92="TRUE" AND E2:E92="INSIDER." I only want to count the instances where both fields match.

    I used the following sumproduct:
    =SUMPRODUCT(('CASE DATA'!A2:A92="TRUE")*('CASE DATA'!E2:E92="INSIDER"))

    However, it keeps returning "0", when I know this is not the case. Any ideas?

    Later, I need to count all instances where A2:A92="TRUE," E2:E92="INSIDER," AND H2:H92="ARCHIVE" OR H2:H92="HISTORICAL SOCIETY".

    Help?

    Thanks,
    Brittany
    Last edited by NBVC; 07-09-2010 at 09:41 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,946

    Re: Sumproduct

    Is TRUE really text and not just the TRUE/FALSE constants.

    =SUMPRODUCT(('Case data'!A2:A92=TRUE)*('Case data'!E2:E92="INSIDER"))
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct

    If the TRUE/FALSE is result of boolean formula.. then don't put quotes around the TRUE or leave out the =TRUE altogether.

    =SUMPRODUCT(('CASE DATA'!A2:A92=TRUE)*('CASE DATA'!E2:E92="INSIDER"))
    or
    =SUMPRODUCT(('CASE DATA'!A2:A92)*('CASE DATA'!E2:E92="INSIDER"))

    the second formula:

    =SUMPRODUCT(('CASE DATA'!A2:A92)*('CASE DATA'!E2:E92="INSIDER")*(('CASE DATA'!H2:H92="ARCHIVE")+('CASE DATA'!H2:H92="HISTORICAL SOCIETY")))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    07-07-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct

    "TRUE" is the actual text, not boolean. I guess I could change it to some other word if that's part of what is messing me up?

    Thanks...

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct

    if you just typed in TRUE then it recognizes it as the Boolean.

    You would need to remove the quotes.

    If you typed an apostrophe before it, or in a cell preformatted as text, then it would be text.

    It kind of works like numbers in this case...

    But to be safer, maybe it is better to change the word..

  6. #6
    Registered User
    Join Date
    07-07-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct with TRUE cell entries

    I changed my "TRUE"s and "FALSE"s to "KNOWN" and "UNKNOWN"; I think the boolean was why I was getting a "0" result.

    What about if I needed to do one that included a range? Is that possible with SUMPRODUCT?

    For example:
    A2:A92 = "KNOWN"
    N2:N92 = greater than/equal to 18

    A2:A92 = "KNOWN"
    N2:N92 = greater than 18/less than 30

    etc., etc., etc.

    Thank you guys so much for your help; sorry I'm so dense.

    Britt

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct with TRUE cell entries

    Do you mean?

    =Sumproduct((A2:A92 = "KNOWN")*(N2:N92>=18))

    and

    =Sumproduct((A2:A92 = "KNOWN")*(N2:N92>=18)*(N2:N92<=30))

  8. #8
    Registered User
    Join Date
    07-07-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct with TRUE cell entries

    I don't think so; that comes out high (54 when it should be 1 for the first and 14 when it should be 11 for the second). So frustrating! I double checked my original data, and that isn't where the problem is.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct with TRUE cell entries

    The first one counts how many times range A has Known while at the same row in range N you have >=18.

    Similary, the second does the same but checks if range is between 18 and 30... if you don't want to include 18 and 30 in second one, take out the equal signs, leaving just > and <, respectively.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct with TRUE cell entries

    You can always post a workbook sample so we can see what is going wrong.

  11. #11
    Registered User
    Join Date
    07-07-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Red face Re: Sumproduct with TRUE cell entries

    Done!

    Let me know if you're able to make heads or tails of it.

    My data is in Sheet 1 - "Case Data." "Thieves" is mostly fixed thanks to your previous help, but I'm still having trouble with the ages. "Repositories" and "Sales" are a complete mess.

    Thank you, so so so so much.

    Britt
    Attached Files Attached Files

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct with TRUE cell entries

    In the Case Data... why are you saying that the results should be 1 and 11? I manually count 50 and 14.

    And so getting 54 and 14 was close, but I see you also have text in column N... that is causing some trouble, so should use:

    =SUMPRODUCT((A2:A92="KNOWN")*(N2:N92>=18)*ISNUMBER(N2:N92))

    and

    =SUMPRODUCT((A2:A92 = "KNOWN")*(N2:N92>=18)*(N2:N92<=30)*ISNUMBER(N2:N92))

  13. #13
    Registered User
    Join Date
    07-07-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct with TRUE cell entries

    I had updated the data since my earlier post, prior to uploading the file, hence the difference in numbers.

    I've tried both formulas, and they are coming out to 0. Sigh.

    Thanks anyway,
    Britt

  14. #14
    Registered User
    Join Date
    07-07-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct with TRUE cell entries

    Nevermind, forgot to inser the reference to sheet 1. THANK YOU!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1