# I need to create a conditional COUNTIF formula

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?

• ✭✭✭✭✭✭

try ISERROR or

IFERROR

to capture erros for your whole formula.

Hope this helps

Stefan

Smartsheet Consulting, Solution Building, Training and Support.

Projects for Processes and for People.

• ✭✭✭✭✭

@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!

• 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?

• ✭✭✭✭✭✭

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

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.

Second step:

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

Hope this helps

Stefan

Smartsheet Consulting, Solution Building, Training and Support.

Projects for Processes and for People.

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

• ✭✭✭✭✭✭

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

Smartsheet Consulting, Solution Building, Training and Support.

Projects for Processes and for People.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!