COUTIFS EXCEL 2010 3 Conditions

October 21, 2011 at 20:47:03
Specs: Windows XP, 4 gig
In Excel 2010 I have a 3 condition Countifs that says if State is AK count if the Beds are 0 and the Revenue is 0. One column has all the states. Another has all the hospitals, another has the number of beds and the last on has the gross patient revenue. I just want to get a count of the number of Hospitals in a particular State where the number of beds is 0 and the revenue is 0. I figured out how to count beds equal 0 (8) and revenue equal 0 (9) but the count should be 7 that meets both criteria. I think I need to use an AND logical but not sure how. Thanks

See More: COUTIFS EXCEL 2010 3 Conditions

Report •

#1
October 21, 2011 at 21:58:10
Since I can't see your spreadsheet from where I'm sitting, I can only offer an example of what might work for you.

With this data set, the formula below will return 2 since there are 2 instances where the state is AK and there is a 0 in both column B and C.

=COUNTIFS(A1:A12, "AK", B1:B12, 0, C1:C12, 0)

	A 	B	C
1	AK	0	0  <---
2	NY	2	1
3	NJ	0	0
4	MI	1	1
5	AK	0	0  <---
6	NY	0	1
7	NJ	5	0
8	MI	0	1
9	AK	1	0
10	NY	0	1
11	NJ	1	0
12	MI	0	0

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 22, 2011 at 21:15:34
Thank you... I thought I had done that but it was returning 3 on mine. Treating is as an "OR" condition. The Hospital data actually exists on another tab of the sheet where I have all the hospitals in the USA and wanted to get a count by state as to how many hospitals met the condiiton of No Beds and No Revenue. I will retry with your logic.

Report •
Related Solutions


Ask Question