Need assistance with a COUNTIFS formula

**ariehc** · 05-02-2013, 12:34 PM

I have a range of cells (K10:K77) that consist of a drop down list of 4 different choices (MIW, Non-MIW, SIP, Self-Employment) and I have another range of cells (L10:L77) that is either ({Blank} or 'y'). When I use the Countif formula I am able to correctly count the number of instance of each of the choices in range K10:K77. But when I try to add the "y" indicator from range L10:L77 using Countifs then I get an error message. The formula I am trying is -- =COUNTIFs(K10:K77, "MIW"; L10:L77 , "y"). When I do this the K10:K77 turns blue.
I want to be able to count the number of MIW, Non-MIW, SIP, Self-Employment that have the "y" indicator from the other range (L10:L77).

Any assistance would be greatly appreciated.

**FDibbins** · 05-02-2013, 01:19 PM

Hi and welcome to the forum

what to you mean "When I do this the K10:K77 turns blue."? Does it allow you to enter the formula and just gives 0 (or some error) for the answer, or it wont even let you enter?

If it will let you enter, then check to make sure that the "y" doesnt have leading/trailing spaces.

perhaps upload a sample workbook (no sensitive data)...
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Jonmo1** · 05-02-2013, 01:32 PM

Try changing

=COUNTIFs(K10:K77, "MIW"; L10:L77 , "y")
to
=COUNTIFs(K10:K77, "MIW", L10:L77 , "y")

**FDibbins** · 05-02-2013, 01:56 PM

hmmmm good catch Jonmo

**ariehc** · 05-02-2013, 02:06 PM

I have attached a sample of the workbook. When I use the formula =COUNTIFS(K10:K77, "MIW"; L10:L77 , "y") the K10:K77 turns blue in the formula line and then gives an error message if I try to execute the formula. The worksheet is called Assessments, the field I am trying to work with is cell K2. If I can get the answer for that field then I can do K3, K4 and K5. So again, I need for cell K2 to indicate the # of outstation appointments that were for the type "MIW". I realize that the value in K2 for the attached sample is incorrect (that entry can be ignored).

Thank you for the assistance.

**ariehc** · 05-02-2013, 02:09 PM

I saw the response by Jonmo after I had uploaded the file. Thank you to you both.. the recommendation worked. When I was looking for possible solutions I saw some examples use a comma and others use a semicolon what is the difference for excel formulas?