Hello,
I'm trying to count a specific range of criteria in a cost study sheet I made. I'm only having an issue when I try and use a range of cells in the COUNTIF or COUNTIFS functions.
Currently, I am using =COUNTIF($C$16:$C$46,H3) and that works fine when counting one piece of criteria. The H3 is the cell that contains the name of the encounter that I'm tracking (Hospital Admission) and cells C16 through C46 have data validation for about 20 different pieces of criteria. A separate table above collects the numbers and dollars from that range (C16:C46). However, when I try and put a range of criteria, it won't count the number or the dollars. For example, the formulas for Hospital Admission, Emergency Room Visit, Primary
Care Visit, and Home Health Visit (=COUNTIF($C$16:$C$46,H3)) all work fine, but when I try and group a bunch of different specialty visits together to be counted in one spot, it doesn't work. I do this because there are about 20 different specialty visits (Cardiology, Neurology, Podiatry, etc.) and I don't want to list them all in the table (H3 - H7), only in the drop down menus in the encounter information section (C16 - C46). So, I have the various specialty visits listed in cells L5 through L26 off to the side and I want them counted under just the Specialty Visit encounter (H6).
Here is an example of what the table looks like where I'm trying to put the formula (C16:C46 contains drop down menus that let the user select between 20 pieces of criteria such as Hospital Admission, Emergency Room Visit, Cardiology, etc.; H3 through H7 contains the encounter types in the table; and E16:E46 contains the dollar amounts that correlate with the encounter types). I bolded the part where the issue is occurring because if I put H6 in the formulas, it works. However, I don't have anything listed in the drop down menu named Specialty Visit, so that doesn't do me any good, haha. I need the criteria listed in cells L5:L26 for Specialty Visit.
Dollars Count Encounter Type
=SUMIF($C$16:$C$46,H3,$E$16:$E$46) =COUNTIF($C$16:$C$46,H3) Hospital Admission (H3)
=SUMIF($C$16:$C$46,H4,$E$16:$E$46) =COUNTIF($C$16:$C$46,H4) Emergency Room Visit (H4)
=SUMIF($C$16:$C$46,H5,$E$16:$E$46) =COUNTIF($C$16:$C$46,H5) Primary Care Visit (H5)
=SUMIF($C$16:$C$46,L5:L26,$E$16:$E$46) =COUNTIF($C$16:$C$46,L5:L26) Specialty Visit (H6)
=SUMIF($C$16:$C$46,H7,$E$16:$E$46) =COUNTIF($C$16:$C$46,H7) Home Health Visit (H7)
Can anyone help me figure out why the range of L5:L26 won't work in either COUNTIF/COUNTIFS functions? Sorry if it's a little confusing. Just let me know if you have any questions. Thanks!
Justin
I'm trying to count a specific range of criteria in a cost study sheet I made. I'm only having an issue when I try and use a range of cells in the COUNTIF or COUNTIFS functions.
Currently, I am using =COUNTIF($C$16:$C$46,H3) and that works fine when counting one piece of criteria. The H3 is the cell that contains the name of the encounter that I'm tracking (Hospital Admission) and cells C16 through C46 have data validation for about 20 different pieces of criteria. A separate table above collects the numbers and dollars from that range (C16:C46). However, when I try and put a range of criteria, it won't count the number or the dollars. For example, the formulas for Hospital Admission, Emergency Room Visit, Primary
Care Visit, and Home Health Visit (=COUNTIF($C$16:$C$46,H3)) all work fine, but when I try and group a bunch of different specialty visits together to be counted in one spot, it doesn't work. I do this because there are about 20 different specialty visits (Cardiology, Neurology, Podiatry, etc.) and I don't want to list them all in the table (H3 - H7), only in the drop down menus in the encounter information section (C16 - C46). So, I have the various specialty visits listed in cells L5 through L26 off to the side and I want them counted under just the Specialty Visit encounter (H6).
Here is an example of what the table looks like where I'm trying to put the formula (C16:C46 contains drop down menus that let the user select between 20 pieces of criteria such as Hospital Admission, Emergency Room Visit, Cardiology, etc.; H3 through H7 contains the encounter types in the table; and E16:E46 contains the dollar amounts that correlate with the encounter types). I bolded the part where the issue is occurring because if I put H6 in the formulas, it works. However, I don't have anything listed in the drop down menu named Specialty Visit, so that doesn't do me any good, haha. I need the criteria listed in cells L5:L26 for Specialty Visit.
Dollars Count Encounter Type
=SUMIF($C$16:$C$46,H3,$E$16:$E$46) =COUNTIF($C$16:$C$46,H3) Hospital Admission (H3)
=SUMIF($C$16:$C$46,H4,$E$16:$E$46) =COUNTIF($C$16:$C$46,H4) Emergency Room Visit (H4)
=SUMIF($C$16:$C$46,H5,$E$16:$E$46) =COUNTIF($C$16:$C$46,H5) Primary Care Visit (H5)
=SUMIF($C$16:$C$46,L5:L26,$E$16:$E$46) =COUNTIF($C$16:$C$46,L5:L26) Specialty Visit (H6)
=SUMIF($C$16:$C$46,H7,$E$16:$E$46) =COUNTIF($C$16:$C$46,H7) Home Health Visit (H7)
Can anyone help me figure out why the range of L5:L26 won't work in either COUNTIF/COUNTIFS functions? Sorry if it's a little confusing. Just let me know if you have any questions. Thanks!
Justin
