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

 

[Allan8514096]

Thanks anyway peter you have already saved me hours of messing around with the first issue which will prevent having to submit manually calculated status reports :P

 

[XOR LX]

Hi,

Perhaps just:

=MIN(IF(B1:B10="STRAT PST 1",IF(K1:K10<>"",K1:K10)))

Regards

 

[Allan8514096]

Hi XOR,

=MIN(IF(KIT_LIST!B5:B10000="STRAT PST 1",IF(K5:K10000<>" ",K5:K10000)))

Returns a 0 value

 

[XOR LX]

Why've you changed the "" to a " "?

Regards

 

[Allan8514096]

Sorry was experimenting, also forgot to add the other sheet references:
FYI this formula lives sheet "MAIN" and the reference ranges on sheet "KIT_LIST"

=MIN(IF(B1:B10="STRAT PST 1",IF(K1:K10<>"",K1:K10)))

=MIN(IF(KIT_LIST!B5:B10000="STRAT PST 1",IF(KIT_LIST!K5:K10000<>"",KIT_LIST!K5:K10000)))

 

[XOR LX]

So what's the result of the formula now, with "" instead of " "? Still 0?

Regards

 

[Allan8514096]

Still a 0 value in a 'general' formatted cell and 00-Jan-00 when the cell is formatted to 'Date'

I tried filling all EXP date cells (K) with a date value (30-Jun-15) and Peter's array returns the correct value but fails as soon as one is blank
Your formula still returns 00-Jan-00 or 0 with all K cells filled.

 

[XOR LX]

And you haven't forgotten to commit it as an array formula?

Regards

 

[Allan8514096]

I had Forgotten at first it was failing cos i had forgotten one of the sheet references then after that i forgot to ctrl+shift+Enter

Working very well now thanks XOR

 

[XOR LX]

Ok, glad we got there in the end!

Cheers