Trouble Coming Up with a Countif Formula...

ECR78

I am trying to figure out a proper formula to count two criteria within one column and a specific date in another column and feel like I’m tried every one except the right now so far and whether I’d need an AND or OR option within the formula or something else. The columns in play are below:

Call Status Column

Left Message

No Answer

Date Column

9/1/23

The formula below counts one of the criteria within the Call Status column, but I am having no luck trying to add a secondary status count. 

=COUNTIFS({Call Status Column}, "Voicemail", {Date Column}, =DATE(2023, 9, 1))

Ultimately, I am looking to count the number of times on a sheet when the Call Status column has either “Left Message” or “No Answer” listed and only when those entries also have the date of September 1, 2023, on the Date Column. All this will feed into a separate formula sheet to help populate dashboards.  

Thanks for any insights in advance. 

Zachary Hall

I believe simply adding two COUNTIFS() statements (one with each set) should work for you:

• =COUNTIFS({Call Status Column}, "Left Message", {Date Column}, DATE(2023, 9, 1))+COUNTIFS({Call Status Column}, "No Answer", {Date Column}, DATE(2023, 9, 1))

I hope this helps!

ECR78

Thanks for the reply.

I've tried combining the two statements previously and the result count comes as zero, which I'm assuming it's looking for both criteria to be listed in one cell, maybe instead of counting them as either or? When I separate the two formulas on their own, I get the correct count for left message and no answer but only when I try to combine the two does the problem occur.

John_Foster

Hi @ECR78,

I created a sheet and added the columns, I did manage to get this working using the formula below.

=COUNTIFS([Call Status Column]:[Call Status Column], OR(@cell = "Left Message", @cell = "No Answer"), [Date Column]:[Date Column], @cell = DATE(2023, 9, 1))

Hope this helps!

John

Zachary Hall

Weird... If John's formula didn't fix it maybe try the one below:

• =SUM(COUNTIFS({Call Status Column}, "Left Message", {Date Column}, DATE(2023, 9, 1)),COUNTIFS({Call Status Column}, "No Answer", {Date Column}, DATE(2023, 9, 1)))