CountIFs formula question

bsaucedo

Hi, I am trying to count the amount of time a drop down is selected within a range of columns that is in a specific location (separate column with it's own values). My goal is to count how many times Location (A-C) have the value Properly demonstrated and Not Properly Demonstrated selected. This is currently the formula I am using :

=COUNTIFS([GENERAL app]:[GENERAL Management], "Properly Demonstrated", [Location:]:[Location:], "Location A") but am receiving a #Incorrect Argument error. Thank you !

KPH

Hi

Your first range looks to be 2 or more columns wide, while the second is just one column (Location:). Both ranges need to be the same size and shape.

You have a few options, you could

1. add additional columns to the location range,
2. add a new column to be checked if any of the GENERAL columns are "Property Demonstrated" and use that in the COUNTIFS,
3. make the COUNTIFS just one column and add multiple COUNTIFS together.

Here are some details on how these options would look.

Option 1 - include other columns in the second range

Add new columns or include those that already exist

=COUNTIFS([General App]:[GENERAL Management], "Properly Demonstrated", [Location:]:[Another column], "Location A")

This works as both ranges are 2 columns wide. Cons are that you might need to add a lot of extra columns next to Location (I don't know how wide your range 1 is). And if you include columns that are in use they may also have the "Location A" string and invalidate your count.

Option 2 - Check box

Add a new column

=COUNTIFS([Any General column is Properly Demonstrated]:[Any General column is Properly Demonstrated], true, [Location:]:[Location:], "Location A")

This works as both ranges are now 1 column wide. Con is you need to also set up the IF to check the box. You can hide the column though.

Option 3 - Multiple COUNTIFS

No change needed to the sheet

=COUNTIFS([General App]:[General App], "Properly Demonstrated", [Location:]:[Location:], "location A") + COUNTIFS([General App]:[General App], <>"Properly Demonstrated", [GENERAL Management]:[GENERAL Management], "Properly Demonstrated", [Location:]:[Location:], "location A")

You can create multiple counter formula (one per General column) and add them together but you need to be careful not to double count. If you only have 2 or 3 columns this is fairly simple, but any more than that is error-prone.

bsaucedo

re-wrote my message

bsaucedo

Thank you, I thought it worked for my sheet but it didn't, General app and General Management are actually the outside columns of a total of 5 columns I am trying to count. I am just trying to count how many times they select Properly demonstrated at Location A in the five columns so the check box wouldn't work. I might find it easier to have two sheets for each location to get the counts

In case you can assist, I was using the formula (=COUNT([GENERAL app]:[GENERAL Management])) to count the total 'General' cells but do you know how can I adjust this formula to count that range off all the responses in location A not including the the other locations?

@KPH

KPH

Hi

I'm not sure how your second question differs from the first. As I understand it, you want to count the "General" cells where there is also a specific value in the location field. You can do that with COUNTIFS. The two ranges will need to be the same size. There are a few ways to ensure the ranges are the same size, as described above. Knowing you have 5 columns I suggest option 1 or 2, and not 3.

So for option 1, if there General columns are 5 columns wide, you must include 5 columns in the range for the location. This is a "quick and dirty" solution that isn't very elegant, but if option 2 doesn't work for you, it could be the answer.

You just need extra columns next to location that you can include in the function so the ranges are the same. If you have 5 General columns, you will need 5 for location.

You can use =[Location:]@row in all the copies so you don't have to manually edit them, can make this a column formula and hide the columns.

Then your COUNTIFS will look like:

=COUNTIFS([General App]:[GENERAL Management], "Properly Demonstrated", [Location:]:[Location copy 5], "Location A")

For option 2 a checkbox can be checked if any of the columns have the value "Properly Demonstrated". You can then have two one-column ranges - the check box and the location - to use in the COUNTIFS. Can you explain why this wouldn't work in your situation?

In this example (you'll need to change the column names):

The check box formula would be:

=IF(OR([General App]@row = "Properly Demonstrated", [general 2]@row = "Properly Demonstrated", [general 3]@row = "Properly Demonstrated", [general 4]@row = "Properly Demonstrated", [GENERAL Management]@row = "Properly Demonstrated"), true, false)

This is then checked for each row that has at least one "Properly Demonstrated".

Then you can use that single column in your COUNTIFS, like this:

=COUNTIFS([Any General column is Properly Demonstrated]:[Any General column is Properly Demonstrated], 1, [Location:]:[Location:], "Location A")