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

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.

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.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History