Help with the COUNTIF and SUMIF functions

Citizen7

New Member
Joined
Mar 7, 2016
Messages
3
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I believe that SUMIF and COUNTIF require a single value or range in that argument. So I don't think you can do it that way.
Check out the technique with SUMPRODUCT that Aladin used here in his first reply: Excel COUNTIF for multiple criteria?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I was hoping you would come to the rescue, Aladin!
I figured if anyone would know the trick to do it, it would be you!:cool:
 

Citizen7

New Member
Joined
Mar 7, 2016
Messages
3
Ahh, I missed the SUM/SUMPRODUCT function. That worked. Thanks for your help, guys!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,651
Members
414,083
Latest member
Mrsash

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
Top