Having Issues with COUNTIFS function

I have a sheet in which I reference another. What I am trying to do is reference two columns from one sheet to count the amount of entries that match the logical expressions.

Here is my current formula.

=COUNTIFS({Copy of Incident Report Range 1}, "Burn", {Copy of Incident Report Range 5}, 1)

What I want the column to do is count how man entries from Range 1 are equal to "burn". Additionally I have a column that's a checkbox column which is the second Range I am referencing in the above formula.

So my end goal would be to count how many rows have both "burn" and the column is checked. If I create both these formula separately in two different columns they work. But when I combine them they return INCORRECT ARGUMENT SET.

Makes me wonder if this is possible. Any help would be appreciated.

Find more posts tagged with

Formulas

IT & Operations

Comments

Paul Newcome

It is possible as I use this same thing for a few different projects. I just tested it again with 2 different sheets including using the checkbox column, and it worked just fine for me.

Could it be that the column type for your target cell is not a text/number type?

Also double check your ranges to ensure that they cover what and only what you need for each one.

Otherwise your formula itself looks fine.

rfrimberger

I use formulas for pretty much the same thing, but my formula looks a little different.

=COUNTIFS([Copy of incident report range 1]:[copy of incident report range 1], = "burn", [copy of incident report range 5]:[copy of incident report range 5], NOT(0))

This could just be a more primitive way of writing formulas, but it works for me. Also, make sure your column names are written exactly as they are on your sheet. Good luck!

Paul Newcome

rfrimberger:

Your formula is most certainly technically sound but definitely more "primitive" as you call it.

When referencing another sheet, the {Range} has the [Column Name]:[Column Name] already built in.

Additionally, checkboxes are checked with the number 1. While your "NOT(0)" works... It is a few more key strokes than "1".

I'm not trying to "pick" or be critical. I'm just offering some advise on efficiency as well as secondary ways to write formulas in case the way you typically write isn't getting the result you're looking for.

What matters is... Do what works for you.

Shawn R

@paul,

The first range is looking at a dropdown column. I'm not sure if that makes a difference or not. If my formulas work individually I would assume that they should work in conjunction and the column type wouldn't matter.

@rfrimberger

What is the reason for referencing the range twice with a colon between? I'm unfamiliar with the way that's laid out or the purpose of doing it.

Shawn R

Alright, so I just deleted the references and started over. Works now. Same formula as posted just needed to delete and re-reference the ranges apparently.

Paul Newcome

Shawn: I had mine set to dropdown as well. I just didn't have it shown in the screenshot. Glad you got it working though.

One thing I have found is that when I am referencing a lot of "Specific Text" in a formula, I end up fat fingering something. A single missed letter one time can throw off an entire formula and frustrate you for days.

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…