+ Reply to Thread
Results 1 to 17 of 17

Two criterias: Sum all rows where: Row contains string + column A contains specific word

  1. #1
    Forum Contributor
    Join Date
    11-23-2015
    Location
    Yada, Wales
    MS-Off Ver
    Office 2013
    Posts
    176

    Two criterias: Sum all rows where: Row contains string + column A contains specific word

    Range B4:E100, want to sum all results of the followng

    * Looking for the string "in" each row of the range, for example "inside". If "in" occurs more than one time in the same row, count only as one.
    * Also, column A in the same row as the string "in" needs to be equal "CIA".

    So, if column A row 5 =CIA, and row 5 contains the string "in", count as one. Sum all the number of rows that matches these two criterias.

  2. #2
    Forum Contributor
    Join Date
    10-19-2012
    Location
    Omaha, Nebraska USA
    MS-Off Ver
    Excel 2010
    Posts
    249

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Hi jokris,

    Here is the formula I would use to determine if the string "in" occurs in any cells between Columns B-E. The formula below would be inserted in Range A4 and then copied down to cell A100 so each row is checked.

    Please Login or Register  to view this content.
    To sum up how many rows have the string "in" in them, paste the following formula in a cell outside of the range "A4:E100" to display the result. When put insert it, make sure you then set your cursor at the end of the formula and then hit "[CNTL] [SHIFT] [ENTER]" so excel recognizes it as an array formula, otherwise it won't calculate.

    Please Login or Register  to view this content.
    Hope that helps,

    Dan

  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: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    4
    KIA
    data
    inner
    data
    data
    CIA
    In
    2
    5
    CIA
    data
    data
    inside
    ink
    -----
    -----
    -----
    -----
    6
    ACI
    data
    data
    data
    data
    7
    DEF
    dink
    data
    data
    data
    8
    CIA
    data
    data
    data
    data
    9
    CIA
    data
    data
    data
    data
    10
    CIA
    pink
    link
    link
    drink


    This formula entered in I4:

    =SUMPRODUCT(--(A4:A10=G4),--(MMULT(--ISNUMBER(SEARCH(H4,B4:E10)),{1;1;1;1})>0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    You could use this formula:

    =SUMPRODUCT((A4:A100="CIA")*(ISNUMBER(SEARCH("in",B4:B100&C4:C100&D4:D100&E4:E100))))

    Hope this helps.

    Pete

  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: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Range concatenation is inefficient.

  6. #6
    Forum Contributor
    Join Date
    11-23-2015
    Location
    Yada, Wales
    MS-Off Ver
    Office 2013
    Posts
    176

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Please Login or Register  to view this content.
    Is it possible to use =INDIRECT("A"&ROW()) to refer to a value instead of =G4?

    Trying but getting Value error.
    Last edited by jokris; 04-09-2016 at 04:49 PM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Quote Originally Posted by Tony Valko View Post
    Range concatenation is inefficient.
    Well, it's only 4 cells, and this seems like a one-off calculation. Is doing ISNUMBER(SEARCH 4 times per row that much better?

    Pete

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

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Quote Originally Posted by Pete_UK View Post
    Well, it's only 4 cells
    It's =ROWS(B4:B100)*4.

    Probably won't notice any difference but I like to let folks know about these things.

    In terms of efficiency...

    Best: array multiplication - (Range1="this")*(Range2="that")
    Better: nested IFs - IF(Range1="this",IF(Range2="that"
    Only if you have to: range concatenation - Range1&Range2="thisthat"

    Of course, if your file is only 10kbs and only has a few formulas you won't know the difference!
    Last edited by Tony Valko; 04-09-2016 at 04:58 PM.

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

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Quote Originally Posted by Pete_UK View Post
    Is doing ISNUMBER(SEARCH 4 times per row that much better?
    I tested the calculation times of these 2 formulas:

    Formula1:

    =SUMPRODUCT(--(A4:A100=G4),--(MMULT(--ISNUMBER(SEARCH(H4,B4:E100)),{1;1;1;1})>0))

    Formula2:

    =SUMPRODUCT((A4:A100="CIA")*(ISNUMBER(SEARCH("in",B4:B100&C4:C100&D4:D100&E4:E100))))

    Did 5 calculations each and got the average of those 5 calculations:

    Formula1: 0.000350
    Formula2: 0.000636

    Tested in Excel 2002 using Charles Williams RangeTimer method:

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

  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: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Quote Originally Posted by jokris View Post
    =SUMPRODUCT(--(A4:A10=G4),--(MMULT(--ISNUMBER(SEARCH(H4,B4:E10)),{1;1;1;1})>0))

    Is it possible to use =INDIRECT("A"&ROW()) to refer to a value instead of =G4?

    Trying but getting Value error.
    It would refer to Ax where x is the row number that the formula is entered on.

    So, if the formula was entered in cell I4 then:

    INDIRECT("A"&ROW()) evaluates to A4.

  11. #11
    Forum Contributor
    Join Date
    11-23-2015
    Location
    Yada, Wales
    MS-Off Ver
    Office 2013
    Posts
    176

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Yes, but it doesn't seem possible to replace G4 with INDIRECT("A"&ROW())?

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

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Try it like this...

    =SUMPRODUCT(--(A4:A10=T(INDIRECT("A"&ROW())))...

    Assuming that whatever is in Ax is a TEXT entry.

  13. #13
    Forum Contributor
    Join Date
    11-23-2015
    Location
    Yada, Wales
    MS-Off Ver
    Office 2013
    Posts
    176

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Doesn't seem to work.

    Perhaps not possible to achieve in Excel.

  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: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Works for me.

    Here's a sample file that demonstrates this.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    11-23-2015
    Location
    Yada, Wales
    MS-Off Ver
    Office 2013
    Posts
    176

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Thank you so much, think I got it now!
    Last edited by jokris; 04-10-2016 at 02:32 AM.

  16. #16
    Forum Contributor
    Join Date
    11-23-2015
    Location
    Yada, Wales
    MS-Off Ver
    Office 2013
    Posts
    176

    Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Double post
    Last edited by jokris; 04-10-2016 at 02:28 AM.

  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: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

    Good deal. Thanks for the feedback!

+ 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] Delete all rows in which column does not contain specific word
    By BVT3030 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-22-2015, 12:57 AM
  2. Replies: 1
    Last Post: 09-11-2014, 10:59 AM
  3. Replies: 1
    Last Post: 07-02-2014, 06:16 PM
  4. [SOLVED] delete rows when column has specific word(s)
    By chrisblackman in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 09-22-2013, 10:21 PM
  5. [SOLVED] Using a macro to select rows with a specific word in a column
    By ahilty in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-15-2013, 04:20 PM
  6. Copy rows containing in column A specific word
    By Odeen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2013, 10:24 AM
  7. Delete rows or Clear contents below a specific word is found in Column A
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2012, 04:08 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