Having Issues with COUNTIFS function

Shawn R

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.

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.