Comparing a year value OR a specific word to the current year.

[dtrimble]

This is probably a pretty straight forward formula, but for whatever reason it's not working out well for me.

Inside a range of cells on one tab -- L2:L18, I have EITHER a year entered as a 4 digit number (e.g. 2014), or the word "Indefinite". The data in this range is the year in which people filling a current job will be rotating/reassigned to another. Those who are "Indefinite" are just that--they do not rotate.

On another spreadsheet, I have a cell where I want to see the total number of people in that L2:L18 range who have a rotation year that is this year or next year. I don't care about months--if their Rotation Year is this year or next year, they should be counted. People with "Indefinite" should never be counted.

Any help would be appreciated.

dt

[Fotis1991]

Did you try?

=COUNTIF(Sheet!L12:L18,2014)

[dtrimble]

Well, sure, that would count any cell value that is =2014, and !=Indefinite. But that's too hard-coded; it needs to programmatically use date functions of some kind to determine if the value in the cell is next year, or equal to this year, while excluding Indefinites.

[Fotis1991]

Maybe somrthing like this?

[dtrimble]

I'm sorry, but this is not helpful. It's not what I'm looking for. I appreciate your suggestions very much, but as I noted in my earlier posts, it has to be formulaic/programattic. I cannot hardcode the year, or pull the current from the value of another cell.


Here's an example of what I was trying to construct, which keeps generating formula errors. Maybe this will clarify a little:


Statistics!F26 -- this is # of employees departing this year or next
Team1!L2:L18 -- the departure/rotation year entered AS TEXT for everyone on Team 1.


In Statistics!F26, I need to construct a formula that would look at L2:L18, compare the year in those cells to the current year -- using FUNCTIONS -- and count if it is equal to next year or this year.

Example:

COUNTIF Team1!L2:L18 = YEAR(TODAY())
OR Team1!L2:L18 = NEXT YEAR (THIS YEAR + 1)
NOT IF Team1:L2:L18 = "Indefinite"


How would I achieve this?

[daddylonglegs]

Try this formula to give a count of this year and next based on today's date

=SUM(COUNTIF(Team!L2:L18,YEAR(TODAY())+{0,1}))

[Fotis1991]

I think that will be helpfull for all of us if you'll upload a small sample workbook.

[kvsrinivasamurthy]

Pl try this
To count cells contain 2012
=COUNTIF (Team1!L2:L18 , YEAR(TODAY())

To count cells contain 2013
=COUNTIF (Team1!L2:L18 , YEAR(TODAY()) +1)

To count cells that does not contain Indefinite

=COUNTIF(Team1!L2:L18, "<>Indefinite")

[dtrimble]

Daddylonglegs,

This works perfectly. Thank you so much.

Can you help me understand the logic here? I had previously not had SUM as part of my formula; I worked only COUNTIF, and am not 100% certain why it needs a SUM as well. I also did not use an array, and was trying to do IF logic to test it against criteria like =YEAR(TODAY), which failed with an error. Looks like you're using the array to basically say YEAR(TODAY) has to be YEAR(TODAY) + 0 (the same as this year), or YEAR(TODAY) + 1 (for next year)?

Thanks again!

dt

[daddylonglegs]

That's about right.....

You could just add 2 COUNTIFs together like kvsrinivasamurthy's suggestion, i.e.

=COUNTIF(Team1!L2:L18,YEAR(TODAY())+COUNTIF(Team1!L2:L18,YEAR(TODAY()+1)

but my suggestion uses an array as the criteria

YEAR(TODAY())+{0,1} = {2012,2013}

so you get an array as the result, e.g. if there are 2 instances of 2012 and 3 or 2013 the result of the COUNTIF is {2,3}

....so you need SUM to sum those two value to give 5

...another way would be to use COUNTIFS (with an "S" on the end) which allows multiple criteria so you could just count years >= 2012 and <= 2013, i.e.

=COUNTIFS(Team!L2:L18,">="&YEAR(TODAY()),Team!L2:L18,"<="&YEAR(TODAY())+1)

[dtrimble]

Makes sense now.

Thank you all for the help!