Count Ifs Specific Year

Shelvia

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!

Eric Law

https://community.smartsheet.com/discussion/114430/count-ifs-specific-year

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)

Shelvia

Hi @Eric Law,

thanks for your response!

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.

Eric Law

https://community.smartsheet.com/discussion/comment/409800#Comment_409800

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?

Shelvia

@Eric Law

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").

Shelvia

@Eric Law

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

okay so i think this worked!

Eric Law

https://community.smartsheet.com/discussion/comment/409823#Comment_409823

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