COUNTIFS for whole Sheet

Kyle Hicks

Hey,

This is probably an easy fix but I am having no luck.

I want to count if anywhere on a sheet has a "4" in a cell.

I also want this to separate the counts by "ITCMW", "ITCT" & "METC"

My current formula is:

=COUNTIFS([Pre Trip Completed]:[Doors/Gates], HAS(@cell, "4"))

I need to include System:System, "ITCMW" in this formula.

Any thoughts?

Jeff Reisman

Can you post some screenshots of your grid layout and sample data?

Are the below column names?

I also want this to separate the counts by "ITCMW", "ITCT" & "METC"

I'm not sure what you're saying here. Can you expand on this?

I need to include System:System, "ITCMW" in this formula.

Kyle Hicks

Below your first question are not the column names. The column name would be "System" as shown in a screenshot.

I also attached a screenshot in an attempt to show I am trying to break this total count of "4's" down by the the categories within the System column. Those categories are ITCMW, ITCT and METC. Sorry for the confusion.

Jeff Reisman

I think I get it now.

The COUNTIFS formula allows you to specify that multiple conditions must be met for the row to be counted. However the below will give you an #INCORRECT ARGUMENT SET error...

=COUNTIFS([Pre Trip Completed]:[Doors/Gates], HAS(@cell, "4"), [System]:[System], "ITCMW")

And breaking the [Pre Trip Completed]:[Doors/Gates] range into individual ranges will only count the rows where ALL the conditions are met, which is not what you want.

So I'm going to suggest adding a helper column to do some of the heavy lifting on a row by row basis, and then using the summary fields to finish the math.

Create a helper column "Total4s" that counts the number of 4s on each row. Use this formula (and convert it to a column formula):

=COUNTIF([Pre Trip Completed]@row:[Doors/Gates]@row, "4")

Then in your Summary fields, use SUMIFS to add up the total number of "4"s where the System field is ITCMW, ITCT, METC, etc.

=SUMIFS([Total4s]:[Total4s], [System]:[System], "ITCMW")

Repeat for "ITCT" and "METC".