Skip blank cells with YEAR function

Paul M Kahl

I’m endeavoring a roll up sheet that will provide sum totals from a single row on a separate Planning Request sheet.

If the source sheet has all cells filled out, the formula seems to work fine:

=SUMIFS({Planning Request Range 2}, {Planning Request Range 1}, [Member]1, {Planning Request Range 3}, "Approved", {Planning Request Range 4}, YEAR(@cell) = YEAR(TODAY()))

 However, a date in the Range 4 column is blank, I get a #Invalid Data Type error in the roll up sheet.

I’m looking for a recommendation on how to skip blank cells when using the YEAR function as listed, or for a better way to parse out years… the goal will be to break out sums for 2020, 2021, etc

Thanks in advance!

Andrée Starå

Hi Paul,

You’ll need to add an IFERROR function before the YEAR.

Try something like this.

=SUMIFS({Planning Request Range 2}, {Planning Request Range 1}, [Member]1, {Planning Request Range 3}, "Approved", {Planning Request Range 4}, IFERROR(YEAR(@cell),0) = YEAR(TODAY()))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi Paul,

You’ll need to add an IFERROR function before the YEAR.

Try something like this.

=SUMIFS({Planning Request Range 2}, {Planning Request Range 1}, [Member]1, {Planning Request Range 3}, "Approved", {Planning Request Range 4}, IFERROR(YEAR(@cell),0) = YEAR(TODAY()))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul M Kahl

Hello

This worked exactly as I hoped. Thank you for the help!

Be well!

Paul

Andrée Starå

Excellent!

You're more than welcome!