Thanks:  0
Likes:  0

# Thread: Help with Tricky 2 criteria COUNTIF type formula

1. ## 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. ## Re: Help with Tricky 2 criteria COUNTIF type formula

Try

=SUMPRODUCT(--(KIT_LIST!B1:B1000="Bulk Fluids"),--(KIT_LIST!Y1:Y1000="Order Me"))

3. ## Re: Help with Tricky 2 criteria COUNTIF type formula

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

I was thinking maybe...
Kit_List

 B C D E F G H I J K L 1 Bag Status 2 Bulk Fluids 1 Order Me 3 Bulk Fluids 2 4 Red Bag 1 Order Me 5 Bulk Fluids 1 6 Red Bag 1 7 Bulk Fluids 1 Order Me 8 Red Bag 1 Order Me

Excel tables to the web >> Excel Jeanie HTML 4

Main

 A B 1 Bag Order Items Count 2 Bulk Fluids 1 2 3 Bulk Fluids 2 0 4 Bulk Fluids 3 0 5 Bulk Fluids 4 0 6 Red Bag 1 2 7 Red Bag 2 0

 Cell Formula 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.

4. ## 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. ## 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.

6. ## Re: Help with Tricky 2 criteria COUNTIF type formula

Originally Posted by Allan8514096
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?

7. ## Re: Help with Tricky 2 criteria COUNTIF type formula

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

Regards
Allan

8. ## 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

 B C D E F G H I J K 1 STRAT PST 1 30/06/2015 2 STRAT PST 1 30/06/2015 3 STRAT PST 2 30/07/2014 4 STRAT PST 1 30/06/2014 5 STRAT PST 2 30/04/2014 6 STRAT PST 2 30/07/2014 7 8 9 10 11 12 13 30/06/2014

 Cell Formula 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

9. ## 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. ## 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.