Help with Tricky 2 criteria COUNTIF type formula

[Allan8514096]

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

 

[Aladin Akyurek]

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?

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!

If you don't want a 0 other than a real-estate 0...

Control+shift+enter:

=IF(
  COUNTIFS(
   KIT_LIST!$B$5:$B$10000,"STRAT PST 1",
   KIT_LIST!$K$5:$K$10000,"<"&9.99E+307),
  MIN(
   IF(KIT_LIST!$B$5:$B$10000="STRAT PST 1",
   IF(ISNUMBER(KIT_LIST!$K$5:$K$10000),
    KIT_LIST!$K$5:$K$10000))),"")

would be one way.