Formula for returning a blank

System

This discussion was created from comments split from: Count all cells in a column that are NOT blank and do NOT contain a date.

Kelly Moore

Hey @lisalettieri

The IF statement doesn't have any criteria to judge against.

Try this

=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))

Does this work for you?
Kelly

lisalettieri

This is great but how do I make it return a blank, instead of ")"? Thank! I tried {Case Consultation Tracker Submission Date}, NOT(ISBLANK(@cell), {Case Consultation Tracker Submission Date}, =""))) but still returns "0".

Kelly Moore

Hey @lisalettieri

Try this

=IF(COUNTIFS({Case Consultation Tracker Submission Date}, NOT(ISDATE(@cell)), {Case Consultation Tracker Submission Date},<>"")>0, COUNTIFS({Case Consultation Tracker Submission Date}, NOT(ISDATE(@cell)), {Case Consultation Tracker Submission Date},<>""))

I assumed you were using the COUNTIFS formula from the other post. If not, adjust two COUNTIFS formula accordingly. If the COUNTIFS returns a zero, the cell will be blank.

Will this work for you?
Kelly

lisalettieri

I tried this to count all cases submitted in Aug which hasn't happened yet so it is 0 but it returns incorrect argument set:

=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, IF(COUNTIFS({Case Consultation Tracker Submission Date}, NOT(ISDATE(@cell), {Case Consultation Tracker Submission Date}, <>"") > 0, COUNTIFS({Case Consultation Tracker Submission Date}, NOT(ISDATE(@cell)), {Case Consultation Tracker Submission Date}, <>""))))

and if I use just your part of the formula, bolded here, it says Unparseable.

This works to count the Submissions in any given month but I want a blank if no submissions that month:

=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)

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))

works

lisalettieri

Actually it didn't work when there are dates in the field only when blank! Help.

Kelly Moore

Hey @lisalettieri

The IF statement doesn't have any criteria to judge against.

Try this

=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))

Does this work for you?
Kelly

lisalettieri

THAT DID IT!!!!!!!!!!!!!!!!!!!!!! Thank you Kelly!!!!!!!!!!!!!!!!!!!

lisalettieri

I thought it did it but as soon as I tried to apply to different months it broke. Says INVALID REF.

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

This one says INVALID DATA TYPE

=IF(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", {Case Consultation Meeting Number}, =1), >0, COUNTIFS({Case Consultation Tracker Submission Date}, >=DATE(2024, 1, 1), {Case Consultation Tracker Submission Date}, <=DATE(2024, 1, 31), {Case Consultation Tracker Submission Status}, <>"NOT Case", {Case Consultation Meeting Number}, =1))

All I did was change the dates!

This is my data:

lisalettieri

I got it to work. thank you again!

Kelly Moore

@lisalettieri

I'm glad you got it to work! If you need anything else, be sure to @mention me. General replies to posts just get lost in the email black hole. ;)