How can I return a blank when none meet critera?

lisalettieri

=IF(AND(COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 8, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 8, 31), {Case Consultation Tracker Range Status}, <>"NOT Case", {Case Consultation Meeting Number}, =1, ISBLANK({Case Consultation Tracker Submission Date}, =true, ""))))

I want to count all dates in Aug, where Meeting date = 1, which works. When I try to get future months to return a blank, does not work. Thanks.

Vince Darrigo

Hi @lisalettieri

Trying to understand the formula here…

IF these 5 things (in the AND statement) are true:

{Case Consultation Tracker Submission Date}, >=DATE(2024, 8, 1)

{Case Consultation Tracker Submission Date}, <=DATE(2024, 8, 31)

{Case Consultation Tracker Range Status}, <>"NOT Case"

{Case Consultation Meeting Number} = 1

ISBLANK({Case Consultation Tracker Submission Date}, =true

How can {Case Consultation Tracker Submission Date} be greater than Aug 1st AND be blank?

(looking past that) THEN what? (what is the value if those things are all true, and what is the value if they aren't all true?)

I want to count all dates in Aug, where Meeting date = 1, which works. When I try to get future months to return a blank, does not work. Thanks.

If you mean "count all dates in August where meeting number = 1" then this does it:

=COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 8, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 8, 31), {Case Consultation Tracker Range Status}, <>"NOT Case", {Case Consultation Meeting Number}, =1)

I can't make heads or tails of the intent behind the IF, the AND or the ISBLANK=true on the same range where you're counting on a value existing. Might you be able to share a screensnip, that might help this make some sense.

lisalettieri

How can {Case Consultation Tracker Submission Date} be greater than Aug 1st AND be blank? Because no cases submitted yet in August. Has not arrived. This is to track who has contacted us for help.

=COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 8, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 8, 31), {Case Consultation Tracker Range Status}, <>"NOT Case", {Case Consultation Meeting Number}, =1)

This is the formula I've been using and it works fine. I want it to return a blank if nothing in the data sheet, i.e. no cases' dates yet. For instance nothing for Sept because Sept has not arrived yet.

Paul Newcome

You would use an IF statement.

=IF(COUNTIFS(………) <> 0, COUNTIFS(………))

lisalettieri

=IF(COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 8, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 8, 31), {Case Consultation Tracker Range Status}, <>"NOT Case", {Case Consultation Meeting Number}, =1), COUNTIFS({Case Consultation Tracker Submission Date}, <>0))

Will return a blank if the data field is blank? So this says, "If all the 1st conditions are met, count if not equal to zero?" Make sense but don't understand why returning a blank, LOL. I guess some things I just have to memorize!

Thanks again!

lisalettieri

Actually it ended up not working in a field where there is a data meeting criteria. It should return the number of cases between my two dates where it's a 1st meeting and I'm getting INVALID DATA TYPE.

=IF(COUNTIFS({Case Consultation Tracker Meeting Date}, >=DATE(2024, 6, 1), {Case Consultation Tracker Meeting Date}, <=DATE(2024, 6, 30), {Case Consultation Meeting Number}, =1), COUNTIFS({Case Consultation Tracker Submission Date}, <>0))

This is what the data looks like:

Paul Newcome

It looks like your syntax is off. Both COUNTIFS should be identical, and the <> 0 portion does not belong in either of them.

lisalettieri

Can you explain in English for me Paul?

The filter from the data sheet (pasted above) results in a different number every time.

The two If(Countifs statements did not work so I went back to =COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 1, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 1, 31), {Case Consultation Tracker Range Status}, <>"NOT Case"). Count dates between the two dates and all that are Not NOT Case. The filter gets 41 vs. formula 44.

With the formula I believe you were suggesting, I get INVALID DATA TYPE:

=IF(COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 7, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 7, 31), {Case Consultation Tracker Range Status}, <>"NOT Case"), COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 7, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 7, 31), {Case Consultation Tracker Range Status}, <>"NOT Case"))

Paul Newcome

The formula I am suggesting uses the same COUNTIFS twice just like the syntax in my previous comment.

=IF(COUNTIFS(………) <> 0, COUNTIFS(………))

Both COUNTIFS in the above would be exactly the same. What the formula quite literally says is "If the countifs is not equal to zero, output the countifs".

lisalettieri

=IF(COUNTIFS({Case Consultation Tracker Submission Date3}, >=DATE(2024, 8, 1), {Case Consultation Tracker Submission Date3}, <=DATE(2024, 8, 31)), COUNTIFS({Case Consultation Tracker Submission Date3}, <>0)) seems to work where ther is not data in Aug. It returns a blank.

BUT if there is data such as in July, says INVALID DATA TYPE.

AND

=IF(COUNTIFS({Case Consultation Tracker Submission Date3}, >=DATE(2024, 7, 1), {Case Consultation Tracker Submission Date3}, <=DATE(2024, 7, 31),{Submission Date3}, <>0)), COUNTIFS({Case Consultation Tracker Submission Date3}, >=DATE(2024, 7, 1), {Case Consultation Tracker Submission Date3}, <=DATE(2024, 7, 31) {Submission Date3}, <>0))

ReSULTS in UNPARSEABLE

lisalettieri

Kelly Moore got it to work:=IF(COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 8, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 8, 31), {Case Consultation Tracker Range Status}, <>"NOT Case", {Case Consultation Meeting Number}, =1)>0, COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 8, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 8, 31), {Case Consultation Tracker Range Status}, <>"NOT Case", {Case Consultation Meeting Number}, =1))

Paul Newcome

@lisalettieri The syntax you got to work is the syntax I was suggesting. Your previous attempts had a completely different syntax.