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?

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

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".

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.