I need to create a conditional COUNTIF formula

McKenna Leighton

I need to count the dates from a separate sheet that occurred in today's week number. The following function works if I choose a range of date fields that are not blank.

=COUNTIF({RANGE}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())).

However, if I choose a range that includes date fields that are blank, it returns "Invalid Data Type" because the formula cannot take the WEEKNUMBER of a blank cell, even if the cell formatting is restricted to Date.

So I need the WEEKNUMBER function to only act on the cell if the cell is not blank.

I've tried COUNTIFS, with the first criteria being NOT(ISBLANK), and the second criteria being identical to above, but it did not work.

Can anyone recommend a solution?

Stefan

HI @McKenna Leighton,

try ISERROR or

https://help.smartsheet.com/function/iserror

IFERROR

https://help.smartsheet.com/function/iferror

to capture erros for your whole formula.

Hope this helps

Stefan

David Joyeuse

Hi @McKenna Leighton

@Stefan is correct you need to use IFERROR, but you need to use it on the WEEKNUMBER function specificly.

=COUNTIF({RANGE}, IFERROR(WEEKNUMBER(@cell),0) = WEEKNUMBER(TODAY())).

Using it on the COUNTIF will always return 0 for your function if there's at least one blank cell in your date column.

Hope it helped!

McKenna Leighton

Amazing!! Thank you David and stefan for your help. I am so excited to automate this properly.

I want to throw a hail mary pass and ask you both if the following automation is possible. It's similar in theory but using more difficult data types.

The range is a column in sheet A. The value in column A can be X, Y, Z. I want to count the number of new X values in the column that occurred in WEEKNUMBER(TODAY()).

Is this possible?

Stefan

Hi @McKenna Leighton,

no problem, now you are talking about combining 2 criteria and therefore have to use COUNTIFS

https://help.smartsheet.com/function/countif

Please note, that the range to be counted now has to sit at the end of the formula!

So first step:

=COUNTIFS(WEEKNUMBER:WEEKNUMBER, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {RANGEtobeCounted}, "X")

Note that you will receive an error if there are cells without date in the range. So you'll have to add IFERROR if that's true for you.

https://help.smartsheet.com/function/iferror

Second step:

=COUNTIFS(WEEKNUMBER:WEEKNUMBER, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR({RANGEtobeCounted}, 0), ="X").

Hope this helps

Stefan

McKenna Leighton

Hi Stefan,

I think I did not describe my use case well enough.The column of interest is not a date column but atext column. Please see new description below:

The range is a column in sheet A. The value in column A can be X, Y, Z (a text field). I want to count the number of new X values in the column that occurred in WEEKNUMBER(TODAY()).

So I'd want to count how many cells in the column have been updated from any string to a specific string in the WEEKNUMBER(TODAY()). (string as in text)

Please let me know if you have any advice.

Thank you for your help!!

Stefan

https://community.smartsheet.com/discussion/comment/263678#Comment_263678

Hi @McKenna Leighton,

I'm not sure I can follow.

So, "Column A" is of the type text and can have values like X, Y, Z and this column A lives in another sheet. Let's call the other sheet "source".

In the "source" you want to count the "X" with the changed/updated date in the current week, right?

If that's correct, then in the "source" you need "column A" and a system column of the type "changed (date)". These to columns will be referenced as a range from the formula in your sheet where you need the metric.

Still the formula

=COUNTIFS(WEEKNUMBER:WEEKNUMBER, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR({RANGEtobeCounted}, 0), ="X")

is ok, you only need to change WEEKNUMBER:WEEKNUMBER -> {RANGEofDate}

Hope that makes sense

Stefan