Count if Sheet Summary Formula Help

Eenmeier

Hello! I have a sheet summary for in-progress accounts that uses the Status as the CountIf variable. The problem is that the Status formula includes accounts that the Live Date is equal to or less than today. I need to omit the ones with Live Date equal to today for the sheet summary formula and am really stuck on how to not get an invalid response error.

Here is the current formula: =COUNTIFS(Status:Status, AND(@cell <> "On Hold", @cell <> "Canceled", @cell <> "Inactivity", @cell <> "Live"))

What I really need it to say is: =COUNTIFS(Status:Status, AND(@cell <> "On Hold", @cell <> "Canceled", @cell <> "Inactivity") AND Live Date is greater than today, or is blank, or is an error

I may have to do a helper column for the Live Date, but I am really hoping some AND logic around the live date can be worked in to avoid that.

Thank you for your ideas!! Erica

Eenmeier

I have used a helper column to accomplish the goal.

Eenmeier

I have used a helper column to accomplish the goal.

Genevieve P.

Hi @Eenmeier

I'm glad you were able to find a resolution!

If you want to eliminate the helper column, the COUNTIFS structure is already one big AND statement. You can list each {range} and the criteria afterwards, like so:

=COUNTIFS(Status:Status, AND(@cell <> "On Hold", @cell <> "Canceled", @cell <> "Inactivity"), [Live Date]:[Live Date], OR(@cell > TODAY(), @cell = ""))

If your Live Date houses a formula and the error you're referencing is a formula error, you'll need to clear out that error before pointing another formula to it. The way to do this is to use an IFERROR statement around whatever is in your Live Date column.

Let me know if this makes sense and works for you!

Cheers,

Genevieve