+ Reply to Thread
Results 1 to 25 of 25

Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

  1. #1
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Hi everyone,

    Hoping I can get some help. Trying to do a sumproduct that counts one of two criteria. The results need to count if either of the following happen:

    1) The number in column J is above 625
    or
    2) the partial text "CA" is in either column M or column

    I came up with the following formula. However I keep getting a 0 answer, which is not correct:

    =SUMPRODUCT(--(Inventory!J3:J10000>625),--(Inventory!M3:M10000="*CA*")--(Inventory!O3:O10000="*CA*"))

    Any help from the gurus would be greatly appreciated.

    Thanks!!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Try

    =SUMPRODUCT(--(Inventory!J3:J10000>625),--(ISNUMBER(SEARCH("CA",Inventory!M3:M10000&"|"&Inventory!N3:N10000))))

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Try this...

    =SUMPRODUCT(--(Inventory!J3:J10000>625),SIGN(ISNUMBER(SEARCH("CA",Inventory!M3:M10000))+ISNUMBER(SEARCH("CA",Inventory!O3:O10000))))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Worked great! Thanks very much, guys!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    You're welcome. We appreciate the feedback!

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    You're welcome.

  7. #7
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Ah, sorry guys. I did a manual count and the answer in the forumla is wrong. Perhaps I didn't explain the criteria well enough:

    1. Column J has a figure greater than 625
    OR
    2. Column M has a text/number figure in it that begins with CA
    OR
    3. Column O has a text/number figure in it that beings with CA

    With both formulas, it seems like it might be counting if 1 AND 2 or 1 AND 3 occurs, exclusively. So as an example, if cell J25 630 and cell M25 has a CA value, it should only be counted once.

    So if ANY of those 3 occur then the cell should be counted. Ideas?

    Thanks very much guys

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Try this version

    =SUMPRODUCT(--((Inventory!J3:J10000>625)+ISNUMBER(SEARCH("CA",Inventory!M3:M10000))+ISNUMBER(SEARCH("CA",Inventory!O3:O10000))>0))
    Audere est facere

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Try

    =SUMPRODUCT(--((Inventory!J3:J10000>625)+(ISNUMBER(SEARCH("CA",Inventory!M3:M10000&"|"&Inventory!N3:N10000)))>0))

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    OK, in that case we just need a simple tweak...

    =SUMPRODUCT(SIGN((Inventory!J3:J10000>625)+ISNUMBER(SEARCH("CA",Inventory!M3:M10000))+ISNUMBER(SEARCH("CA",Inventory!O3:O10000))))

    I'm assuming that you want a row by row count and not a cell by cell count.

    For example, if this is your data on row 5:

    J5 = 1000
    M5 = XX
    O5 = CA here

    That gets counted as 1 even though 2 cells meet the conditions.
    Last edited by Tony Valko; 08-19-2013 at 09:50 AM.

  11. #11
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Thanks guys. Huh, the result is still about 40 counts higher than its supposed to be via manual count. Seems like there's some double counting here.

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Can you post a sample book displaying this behaviour?

  13. #13
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    I note that text has to begin with "CA" - SEARCH looks for it anywhere in the cell - hence this version should be better

    =SUMPRODUCT(--((Inventory!J3:J10000>625)+(LEFT(Inventory!M3:M10000,2)="CA")+(LEFT(Inventory!O3:O10000,2)="CA")>0))

    Note: that isn't case-sensitive - it will include rows where text in columns M and O begins with "Ca","CA","ca" or "cA".

    Also assumes that you only have numbers in column J, not any text - if that isn't the case change to:

    =SUMPRODUCT(--((Inventory!J3:J10000>625)*ISNUMBER(Inventory!J3:J10000)+(LEFT(Inventory!M3:M10000,2)="CA")+(LEFT(Inventory!O3:O10000,2)="CA")>0))
    Last edited by daddylonglegs; 08-19-2013 at 10:17 AM.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Can you post a SMALL sample file so we can see what your data looks like?

    SMALL = about 20 rows worth of data.

    Make sure you tell us what result you expect.

  15. #15
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Hi guys,

    DLL, that brought the count down a bit, but its still doesn't seem to match the manual count. I have attached a sample template for you guys. Columns aren't exactly the same, but the figures are.

    My manual count is 87.

    Thanks very much for your help, everyone.
    Attached Files Attached Files

  16. #16
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    I count 129

    =SUMPRODUCT(--((Sheet1!A2:A10000>625)+(LEFT(Sheet1!B2:B10000,2)="CA")+(LEFT(Sheet1!C2:C10000,2)="CA")>0))

    I also put this in columnd D to demonstrate
    =OR(A2>625,LEFT(B2,2)="ca",LEFT(C2,2)="ca")
    And filtered for TRUE
    Shows 129 records.

    EFGroovicles.xls

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Quote Originally Posted by Groovicles View Post

    My manual count is 87.
    Can you explain how you arrive at that result?

    There are 129 ROWS where at least one cell in the row meets the conditions.

  18. #18
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Huh. I'm filtering the three columns and counting as follows:

    Count 1: Column A - filter to greater than 625. Column B - filter to equals blank. Column C - filters to equals blank. Total 69
    Count 2: Column A - filter to less than or equal to 625. Column B - filter to does not equal blank. Column C - filter to equals blank. Total 14
    Count 3: Column A - filter to less than or equal to 625. Column B - filter to equals blank. Column C - filter to does not equal blank. Total 4

    Total 87

  19. #19
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    The AutoFilter does AND criteria, not OR..
    so it's counting if Column A > 625 AND Column B is not blank AND Column C is not blank.

    But you've clearly been saying you want OR.

  20. #20
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Are you saying you want to count (A>625)+(B begins with ca)+(c begins with ca)
    Regardless if those 3 creteria occur on the same row??

    then it's
    =COUNTIF(A:A,">625")+COUNTIF(B:B,"ca*")+COUNTIF(C:C,"ca*")

  21. #21
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Hi Jonmo1,

    Its the only way I could count individual occurances using the filter. It seems the formulas which have been suggested may have been double counting. Example: if A25 = 630, B25 = CA-blablah, C25 = blank, then total count equals 2 where as i'm looking to have it count once.

    I'm sure its me, everyone. Sorry for any confusion.

  22. #22
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    At this point I'm confused as to exactly how/what you want to count.

    Based on this small sample what result do you expect:

    Please Login or Register  to view this content.

  23. #23
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    ok, post another book..
    With smaller sample set, about 20 rows..

    And This time write in a cell the count you expect.
    And highlight ALL ROWS that you say are included in that count, and explain WHY each row is included or excluded from the count.


    It may be important to note that the formulas we are providing are counting how many ROWS meet ALL 3 of your criteria.
    Not how many individual cells meet each individual criteria.

  24. #24
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Hi Tony,

    Total count should be 5.

  25. #25
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

    Quote Originally Posted by Groovicles View Post
    Hi Tony,

    Total count should be 5.
    OK, so you want to count a ROW if any cell on that row meets the condition.

    Try this...

    =SUMPRODUCT(SIGN((A1:A6>625)+(LEFT(B1:B6,2)="CA")+(LEFT(C1:C6,2)="CA")))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumproduct with multiple criteria including partial search
    By Groovicles in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-29-2013, 01:30 PM
  2. Replies: 1
    Last Post: 07-10-2012, 07:39 AM
  3. hlookup and multiple criteria sumproduct to analyze text and dates
    By cdl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2008, 09:12 AM
  4. sumproduct partial text count
    By Ribeye in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2006, 02:50 PM
  5. [SOLVED] Sumproduct with multiple text criteria
    By Robert in forum Excel General
    Replies: 2
    Last Post: 12-06-2005, 01:45 PM

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