Help on formula: count if with multiple criteria

Paul Newcome

The problem is with the second bit.

For multiple range/criteria sets you need countifS. But you don't have a criteria set for the Friendliness range.

You are going to want to remove the Friendliness range and see if that works for you.

Megan Harry

@Paul Newcome

I want to divide the first section by the total entries within the specified date range in the friendliness column, not all rows in the sheet. Don't I need the friendliness in there to specify that? If it isn't possible let me know.

Paul Newcome

If you only have the date range/criteria, even though it will EVALUATE all rows in the sheet, it will only COUNT the rows within that date range. Give it a shot and let me know if it works. If you need to, break it down into a few different cells to see each portion working individually.

Cell 1 = COUNTIFS with Friendliness column

Cell 2 = COUNTIFS without Friendliness column

Cell 3 = Cell 1 / Cell 2

As long as those numbers are working, you can combine each of the pieces into a single, longer formula.

Megan Harry

@Paul Newcome

I am using this formula and getting the error #DIVIDE BY ZERO

=COUNTIFS(Friendliness:Friendliness, OR(@cell = "Very Friendly", @cell = "Friendly"), Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))) / COUNTIFS(Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31)))

I think I must be doing something wrong?

Paul Newcome

Becasue we have two separate functions, try breaking it out in two different cells. I believe it is going to be an issue with the second, but I want to confirm before troubleshooting.

Megan Harry

@Paul Newcome

It still didn't work. It could be something I did wrong, but I am not sure.

I really appreciate your help with this!

Paul Newcome

I didn't expect it to work. I am just trying to narrow down exactly where the issue is coming from.

Break it out like this and let me know exactly what the two outputs are.

Cell 1:

=COUNTIFS(Friendliness:Friendliness, OR(@cell = "Very Friendly", @cell = "Friendly"), Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31)))

Cell 2:

=COUNTIFS(Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31)))

Megan Harry

@Paul Newcome

Both gave me 0. Which is correct.

Paul Newcome

Ok. It is not possible to divide a number by zero. So if the output for the second portion is zero, then it will always throw the #DIVIDE BY ZERO error.

This is actually a pretty straightforward fix. We just need an IFERROR function to replace the error with whatever output you would desire.

=IFERROR(original formula, "desired output")

=IFERROR(COUNTIFS(Friendliness:Friendliness, OR(@cell = "Very Friendly", @cell = "Friendly"), Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))) / COUNTIFS(Date:Date, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))), "desired output")

Just replace "desired output" with whatever you want the formula to display in the event that there is an error to include when the second function (the overall count of rows within the date range) equals zero.

Megan Harry

@Paul Newcome

Paul, you are so great! Thank you so much for the help with this!! It worked! I am super excited!

Benjamin Brunnette

@Paul Newcome

I have a similar question to what has been asked previously. I'm trying to use a =COUNTIF formula if two things are true. Here's the formula I'm using, which looks to be correct based on previous comments but it's coming back with an error:

I'm referencing this sheet below. This info is from a survey. In this example, I'm trying to count only if the event = "Catalyst Conference & Awards Dinner" and the answer to the question = "Agree".

I'm not sure why it's not working... any help is greatly appreciated!

Paul Newcome

@Benjamin Brunnette Try using a COUNTIFS instead (with the S on the end).

Jessica Zahner

Hi, jumping on this wagon. I'm trying to use the COUNTIFS function to total up certain status aircraft (Active, Active/Plan Refurb, and Refurb) with a particular model (example Bell). I have tried MANY formulas, here are a few. I have been able to return total with just Active as my criterion, but I have 3. Tried using OR, =, CONTAINS, many things. Can anyone see what I am doing wrong?

=COUNTIFS({Aircraft Status}, "Active" OR "Active/Plan Refurb" OR "Refurb", {Make}, Make@row

 

=COUNTIFS({Aircraft Status}, = "Active", OR

{Aircraft Status}, = "Active/Plan Refurb", OR {Aircraft Status} = "Refurb"

{Make}, Make@row)

 

=COUNTIFS({Aircraft Status}, "Active") +COUNTIFS({Aircraft Status}, "Active/Plan Refurb"), + COUNTIFS({Aircraft Status}, "Refurb" {Make}, Make@row)

 

=COUNTIFS({Aircraft Status}, "Active") +COUNTIFS({Aircraft Status}, "Active/Plan Refurb"), + COUNTIFS({Aircraft Status}, "Refurb") {Make}, Make@row)

Paul Newcome

@Jessica Zahner Try this one...

=COUNTIFS({Aircraft Status}, OR(@cell = "Active", @cell = "Active/Plan Refurb", @cell = "Refurb"), {Make}, Make@row

Jessica Zahner

@Paul Newcome WOW this worked (no surprise) THANK YOU! but I do have a beginning level question, do you just type in @cell? I assume it means to look at a specific cell, but I have never used that in any of my formulas that I have created.