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