COUNTIF for activity in current YEAR (intake sheet data)

ShaRobinson

I think this should be a simple one for experts, but after searching Support cases for a few hours, I can't make any more progress.

Formula Objective: I want to make certain information from my Portfolio Intake Sheet available for a Dashboard widget, so first I have to compile the data into usable information somewhere.

My information will ultimately be more robust than this, but I'm stuck at this early phase — so if I can gain my wings here, I should be good to go : )

I want to count all projects that should or did Complete in a particular Year (starting with "this year"). Also, not knowing the best data source for my future widget, I'm trying this two ways (Solutions #1 and #2 here) — but get the same errors - invalid data type.

Solution 1. added 3 columns to the Intake Sheet directly, and trying to gather my information right there. (there's an image for this one)

Solution 2. alternate method, trying to add formula to the (Intake) Sheet Summary, and that only uses new Column A for data — for now, until I can get unstuck (this one might be easier to poke holes at and assist me!)

So I added columns to the Intake Sheet shown in image below: A, B, C

1. Failing Solution #1 - using all three columns A, B, C

Col A. - I converted intake "Cust DC Date" at left to "DC YEAR" - 4 digit YEAR. This new column A is formatted as a Date and uses this formula, otherwise I couldn't format as a Date w/o error (=YEAR(@row) + ""). I dragged it down (results in "Invalid data type" where there is no row data yet — but ok..).

(Also, my downstream results were the same whether I had this column A formatted as Date or Text/Number.)

Col B. This column I entered a few sequential static years (2023-2026), for use in the next column C, but note this is formatted as text/number (←could this be part of my problem?)

Col C.. This KEY column is supposed to Tally based on looking in Column A range for data "@row" in Column B. Formula is =COUNTIF([DC YEAR]:[DC YEAR], IFERROR(YEAR(@cell), 0) = [list of years]@row).

I'm so stuck! MUCH simpler, if we want to just look at my Solution 2? See after this image..

Solution 2: Using (intake) Sheet Summary instead.. using new column A "DC YEAR" range, just count if that year is current year, that's it. (I don't love it, but a different way of looking at the data, as a 1st step, then I'd build from there.)

=COUNTIF([DC YEAR]:[DC YEAR], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

I still get Invalid Data Type results, but would have done back-flips if it had counted 9 for the "2024" row, 2 for "2025" row, and 0 for "2023" and "2026".

Essentially, I'll be reporting how many projects "shipped" in each of the prior years, and then trending ship for current year.. and also subsequent year.. I'll just have to tweak each new year.. But again, starting with baby steps.. let's return SOME valid data! :)

Thanks!

Sharon

KPH

Hi Sharon

I think you have read too much and overcomplicated things and are now in a pickle. Hopefully this will help.

Three problems and one thing you thought was a problem and isn't 😀:

1

In Col A you have added "" to the end of the year; probably as a workaround because you have formatted the column as date type and date type doesn't like just years (it is expecting the day and month as well as year). By adding the nothing you are converting the year into text rather than date so it will display.

If you make the column a text column and use =YEAR(@row) instead, then you will just have a 4 digit number in a text column that you can match. Simpler is easier.

2

In Col B you just have text for the year which you think could be a problem. This is fine. If you have text in Col A and Col B you can match.

3

In Col C you have used a formula that is extracting the year from the dates in Col B, which is not going to work as you don't have a date in Col B from which the year can be extracted. You just have years.

You don't need to use the years function here. A simple =COUNTIF([DC YEAR]:[DC YEAR], [list of years]@row) will do what you need

4

And finally (and this isn't really fair as it will prevent everything you try appear to fail so you go around in circles), because you have invalid data type errors within Col A, the count function in Col C will always return invalid data type even if there is something to count (the error sort of throws the formula off).

You can add some error handing to the formula in Col A to display nothing if the value in is not a date. This will remove the invalid data type message and the formula in Col C will work.

=IF(ISDATE([Cust DC Date]@row), YEAR([Cust DC Date]@row), "")

Here is how the sheet looks with the 3 changes. All columns are text/number except Cust DC date

I hope this helps and you are doing backflips real soon. Any questions, just ask.

KPH

Hi Sharon

I think you have read too much and overcomplicated things and are now in a pickle. Hopefully this will help.

Three problems and one thing you thought was a problem and isn't 😀:

1

In Col A you have added "" to the end of the year; probably as a workaround because you have formatted the column as date type and date type doesn't like just years (it is expecting the day and month as well as year). By adding the nothing you are converting the year into text rather than date so it will display.

If you make the column a text column and use =YEAR(@row) instead, then you will just have a 4 digit number in a text column that you can match. Simpler is easier.

2

In Col B you just have text for the year which you think could be a problem. This is fine. If you have text in Col A and Col B you can match.

3

In Col C you have used a formula that is extracting the year from the dates in Col B, which is not going to work as you don't have a date in Col B from which the year can be extracted. You just have years.

You don't need to use the years function here. A simple =COUNTIF([DC YEAR]:[DC YEAR], [list of years]@row) will do what you need

4

And finally (and this isn't really fair as it will prevent everything you try appear to fail so you go around in circles), because you have invalid data type errors within Col A, the count function in Col C will always return invalid data type even if there is something to count (the error sort of throws the formula off).

You can add some error handing to the formula in Col A to display nothing if the value in is not a date. This will remove the invalid data type message and the formula in Col C will work.

=IF(ISDATE([Cust DC Date]@row), YEAR([Cust DC Date]@row), "")

Here is how the sheet looks with the 3 changes. All columns are text/number except Cust DC date

I hope this helps and you are doing backflips real soon. Any questions, just ask.

ShaRobinson

BACKFLIPS indeed!! KPH - you explained that Soooo well, I understood everything you were saying. It worked perfectly (of course!). And you just added some bright shiny tools to my toolbox — thank you!!

KPH

💃 (happy dance!)

Well done you, enjoy the new tools!