Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Help with Tricky 2 criteria COUNTIF type formula

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help with Tricky 2 criteria COUNTIF type formula

    Hi all,
    I have a spreadsheet which we use to track the consumables of aero-medical evacuation medical kits. I am trying to make a formula to count the number of times value "X" appears in column B AND value "Y" appears in column L and return a number.

    To put this in an example
    Sheet "KIT_LIST"
    Column B details the name of the bag Eg "Bulk Fluids 1" "Red Bag 1" (non grouped and always changing so ranges are out)
    Column L displays "Order Me" when such action is required (otherwise blank).

    Formula in words:
    cell value = number of rows where columnB ="Bulk Fluids 1" AND columnY="Order Me"

    This formula would be on a different sheet named "MAIN"
    I would then need to amend the column and criteria values to return each bag types value.
    I will then use these values to make a reporting page to show the status of each bag.

    Seems simple enough but havent got it yet...

    Kind Regards

    Allan

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    Try


    =SUMPRODUCT(--(KIT_LIST!B1:B1000="Bulk Fluids"),--(KIT_LIST!Y1:Y1000="Order Me"))
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    Given that Y is L ???????

    I was thinking maybe...
    Kit_List

     BCDEFGHIJKL
    1Bag         Status
    2Bulk Fluids 1         Order Me
    3Bulk Fluids 2          
    4Red Bag 1         Order Me
    5Bulk Fluids 1          
    6Red Bag 1          
    7Bulk Fluids 1         Order Me
    8Red Bag 1         Order Me


    Excel tables to the web >> Excel Jeanie HTML 4

    Main

     AB
    1BagOrder Items Count
    2Bulk Fluids 12
    3Bulk Fluids 20
    4Bulk Fluids 30
    5Bulk Fluids 40
    6Red Bag 12
    7Red Bag 20

    Spreadsheet Formulas
    CellFormula
    B2=COUNTIFS(Kit_List!B:B,A2,Kit_List!L:L,"=Order Me")


    Excel tables to the web >> Excel Jeanie HTML 4


    Hope that helps.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  4. #4
    New Member
    Join Date
    May 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    Tried
    =SUMPRODUCT(--(KIT_LIST!B5:B10000="STRAT PST 1"),--(KIT_LIST!L5:L10000="Order Me"))

    and get #VALUE (a value used in the formula is the wrong data type)

    Column B is a text cell
    Column L is a formula which returns "Order Me" OR "No" ( =IF(DAYS360(K313,AC313,FALSE)<-90,"No","Order Me") where K= expirt date cell and AC=TODAY() )

    Is this the issue?


    Regards

    Allan

  5. #5
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    That shouldn't be the issue.

    Is that the exact formula that you used, copied from the formula bar? SUMPRODUCT formulas will return #VALUE if the range sizes are not exactly the same.
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,896
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    Quote Originally Posted by Allan8514096 View Post
    Tried
    =SUMPRODUCT(--(KIT_LIST!B5:B10000="STRAT PST 1"),--(KIT_LIST!L5:L10000="Order Me"))

    and get #VALUE (a value used in the formula is the wrong data type)

    Column B is a text cell
    Column L is a formula which returns "Order Me" OR "No" ( =IF(DAYS360(K313,AC313,FALSE)<-90,"No","Order Me") where K= expirt date cell and AC=TODAY() )

    Is this the issue?


    Regards

    Allan
    The SumProduct formula is correct. Do you have any error value in one of the ranges the formula refers to?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    May 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    Aladin,
    There was 1 error hiding in the thousands of rows but i found it haha working fine now

    Peter,
    Thank you very much for your prompt help!


    In the same vein, the final calculation i need to do is show the next expiry date (minimum value) of the bag "STRAT PST 1" ie

    cell value= look at colK and return the minimum date of all the rows containing "STRAT PST 1" in colB.

    B------------------K
    STRAT PST 1 30/6/15
    STRAT PST 1 30/6/15
    STRAT PST 2 30/7/14
    STRAT PST 1 30/6/14 (return this value as the next Expiring row)
    STRAT PST 2 30/4/14 (DO NOT return this value as its not STRAT PST 1)
    STRAT PST 2 30/7/14


    Thankyou in advance

    Regards
    Allan

  8. #8
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    See the example below.

    Note 1: this is an array formula which must be entered using CTRL + Shift + Enter, not just Enter.

    Note 2: You need to format the result cell as Date.

    Sheet2

     BCDEFGHIJK
    1STRAT PST 1        30/06/2015
    2STRAT PST 1        30/06/2015
    3STRAT PST 2        30/07/2014
    4STRAT PST 1        30/06/2014
    5STRAT PST 2        30/04/2014
    6STRAT PST 2        30/07/2014
    7          
    8          
    9          
    10          
    11          
    12          
    13  30/06/2014       

    Spreadsheet Formulas
    CellFormula
    D13{=MIN(IF(B1:B10="STRAT PST 1",K1:K10))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  9. #9
    New Member
    Join Date
    May 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    Peter,
    =MIN(IF(KIT_LIST!$B$5:$B$10000="STRAT PST 1",KIT_LIST!$K$5:$K$10000))

    Worked great except it returns blank date cells (K) within the range as 00-Jan-00, could you make ignore those blanks and return the minimum non blank value?

  10. #10
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Help with Tricky 2 criteria COUNTIF type formula

    That is tricky, for me at least.

    If you can replace the blanks in column K with a non-numeric like ? then my array formula will work.

    Otherwise hope that Aladin returns to this thread!
    HTH, Peter
    Please test any code on a copy of your workbook.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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