Help with Countifs formula

JennS_

I am trying to count the number of "failures" within a certain range of columns (about 67 columns) that also are specific to a cabin name "Agnis" (for example). I keep getting an incorrect argument or error on the formula.

This is the other sheet I am referencing:

The range of "failures" is columns starting at "Driveway sign rating" and extends over about 60+ more columns so I am trying to capture all those columns. Then, I want to only count them if the Cabin name is specific. For this example, it would be in the "Cabin Name" column.

I am having trouble with the ranges I select and it returns an error. Note that this example is a form entry sheet.

Help is appreciated! Thanks.

Paul Newcome

You are going to need a helper column on your source sheet with a COUNTIFS to output the number of Failures on each row. Then you would SUM this helper column on your metrics sheet.

If you want to also do this for the other ratings, you would need to add a helper column for each rating.

Hollie Green

The only way I know to do it is to add multiple countifs formulas. The range of more than one column doesn't seem to work with the countifs formula. It works with countif but not countifs.

Example

=COUNTIFS({Driveway Sign Rating},"Failure",{Cabin Name},"Agnis")+COUNTIFS({Driveway/Parking Spot Rating},"Failure",{Cabin Name},"Agnis") continue adding for each of your columns

Paul Newcome

@Hollie Green The reason the multi-column range isn't working is because all ranges within a function MUST be of the same shape/size. In the COUNTIF you only have one range. In the COUNTIFS you have two ranges. One is multiple columns and the other is single columns. If both were single columns or both were multiple columns (and the same number of columns) then it would work.

Generally speaking I do usually go with the COUNTIFS + COUNTIFS solution, but in this case that means stacking 67 individual COUNTIFS in there. That's why I went with the helper columns on the source sheet to get the totals for each score. 5 COUNTIFS is a lot easier to manage than 67 COUNTIFS.

JennS_

I'm back again with another modification to this formula. How would I update this to only count if the "cabin type" is a 1B or 2B etc. @Paul Newcome

I have this formula (which is in my helper columns)

=COUNTIFS([Driveway / Parking Spot Rating]2:[Entry Lock Type]2, OR(@cell = "", @cell <> ""))

but I want it to only count that range if the cabin type column is a specific identifier (ie 1B). This is because it is skewing the number of cells that it is adding, because some cells are only related to a certain type of cabin.

Paul Newcome

You would use an IF statement like so:

=IF([Cabin Type]@row = "1B", COUNTIFS([Driveway / Parking Spot Rating]2:[Entry Lock Type]2, OR(@cell = "", @cell <> "")))