Formula not working

FGCC Smartsheet

Can someone please help me with this formula? I've worked on it since 7:30 am it's now 10:15 am.

=COUNTIFS({complaints Range 1}, NOT(ISBLANK(@row)), {complaints Range 2}, >=DATE(2023, 1, 1), {File a complaint Range 3}, <=DATE(2023, 12, 31))

SteyJ

convert to column formula, then add an iferror function

=IFERROR(COUNTIFS({complaints Range 1}, <>"", {complaints Range 2}, >=DATE(2023, 1, 1), 
{File a complaint Range 3}, <=DATE(2023, 12, 31)), "Bad Ranges")

Itai

Hey @FGCC Smartsheet

Can you share a screenshot please? It will make it easier to understand.

Also, what are you trying to achive?

SteyJ

try this:

=COUNTIFS({complaints Range 1}, <>"", {complaints Range 2}, >=DATE(2023, 1, 1), 
{File a complaint Range 3}, <=DATE(2023, 12, 31))

FGCC Smartsheet

@SteyJ That returned an invalid argument.

@Itai

Range 1

Range 2

Range 3

SteyJ

convert to column formula, then add an iferror function

=IFERROR(COUNTIFS({complaints Range 1}, <>"", {complaints Range 2}, >=DATE(2023, 1, 1), 
{File a complaint Range 3}, <=DATE(2023, 12, 31)), "Bad Ranges")

Itai

Hey @FGCC Smartsheet

I think the issue is with the NOT(ISBLANK()) - I would recommend to add a new column in the database where the range is with a simple formula that will return 1 if its not blank then you can use it as a criterion for this formula.

=IF([Column]@row = "", 0, 1)

FGCC Smartsheet

@Itai You are also very helpful. Thank you!