Help with Tricky 2 criteria COUNTIF type formula

Allan8514096

New Member
Joined
May 6, 2013
Messages
14
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try


=SUMPRODUCT(--(KIT_LIST!B1:B1000="Bulk Fluids"),--(KIT_LIST!Y1:Y1000="Order Me"))
 
Upvote 0
Given that Y is L ???????

I was thinking maybe...
Excel Workbook
BCDEFGHIJKL
1BagStatus
2Bulk Fluids 1Order Me
3Bulk Fluids 2
4Red Bag 1Order Me
5Bulk Fluids 1
6Red Bag 1
7Bulk Fluids 1Order Me
8Red Bag 1Order Me
Kit_List


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



Hope that helps.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.

Excel Workbook
BCDEFGHIJK
1STRAT PST 130/06/2015
2STRAT PST 130/06/2015
3STRAT PST 230/07/2014
4STRAT PST 130/06/2014
5STRAT PST 230/04/2014
6STRAT PST 230/07/2014
7
8
9
10
11
12
1330/06/2014
Sheet2
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top