Count Ifs Specific Year

Options
✭✭

I am trying to Count Ifs the word "Bidding" appears in a column based on the Bid date being in 2024.

=COUNTIFS({Electrical Status}, CONTAINS([Primary Column]@row, @cell), {Bid Date}, YEAR(@cell) = 2024)

this is what i have (primary column is referencing the word bidding).

I am pretty sure it has something to do with the date portion, maybe because there is TBD in the column as well.

Source Sheet

Sheet with Calculations

Help much appreciated!

• ✭✭✭✭✭✭
Options

I don't think you need the CONTAINS part, try this. You should be getting the answer of 0.

=COUNTIFS({Electrical Status}, [Primary Column]@row, {Bid Date}, YEAR(@cell) = 2024)

• ✭✭
Options

Hi @Eric Law,

Unfortunately, that still didn't work. My formula worked without the date range and criteria. I should be getting the answer of 1 because there is 1 project listed "bidding" with the date of 1/15/24.

• ✭✭✭✭✭✭
Options

So, I do know the the YEAR function gets kind of... buggy when it's referencing a different sheet. Would you be able to put a year column in your main sheet and reference that column?

• ✭✭
edited 12/15/23
Options

I would preferrrrr not to. i did successfully use the YEAR function the same sheet just a couple rows above:

=SUMIFS({Sell Price Elect}, {Date Capture}, YEAR(@cell) = 2024)

this returned everything fine.

I was wondering if maybe i needed to include some sort of If error because the Bid Date column had some non dates in it ("TBD").

• ✭✭
Options

=COUNTIFS({Electrical Status}, [Primary Column]@row, {Bid Date}, IFERROR(YEAR(@cell), 0) = 2024)

okay so i think this worked!

• ✭✭✭✭✭✭
Options

That makes sense! I always forget about throwing in an IFERROR.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!