Countifs with nested year look up

Hello again, Wizards of OZ.

Back with what I believe to be a major brain fart, hope you can help.

I have a 2248 row spreadsheet with more than a few columns, which would take me a long time to scrub to make it safe for posting(we're talking days, not hours); in this spreadsheet is a fairly simple countifs that refuses to operate properly.

=COUNTIFS(UIC,G2,Table13[Year],"<AB1")

Following the formula, this should look at the UIC column and count all the matches to the data in cell G2, then look at the results and match all the ones in the Year column that are less than the value in cell AB1. The problem is, it won't do a "<" in the nested argument. I have tried "<AB1" "<(AB1)" and all sorts of variations along those lines, it will not allow me to insert them(formula errors), or it tries to count them as literally <AB1 (anything that has a value less than the letters ab1 is counted, making all dates count).

I initially wanted to make it self-contained with a

=COUNTIFS(UIC,G2,Table13[Year],"<=TODAY(YEAR)-1")

type of thing...but I'm sure you are all aware that that did not work.

The essence of it is that it needs to compare the values in the "year" column against the current year-1.

Help me, Oz-e won kenobi, you're my only hope!

Edit : silly thought... something along these lines, maybe?

=COUNTIFS(UIC,G3,Year,"=(TODAY())(=TEXT"YYYY")-1")

I'm going to keep poking around until something sticks!

Re: Countifs with nested year look up

forum.ozgrid.com/index.php?attachment/36863/I dumped over 90% of the file trying to make this thing safe to send, and I'm still not 100% sure I like having this posted on the web. The yellow highlighted column is where the formula is located. Realistically, this question is more about how to nest a <=TODAY statement than it is about how it will be used in the sheet. If one was going to nest an =TODAY()-365 inside of an IF statement, the code

=IF(A2<=TODAY()-365,[argument],[else argument])

would be fairly efficient a way to do so, correct? If, Then, Else.... I virtually never have a use for a single IF statement, mine is typically a COUNTIF, COUNTA, or COUNTBLANKS or some combination of the 3, so I could be waaaay off the mark.

Look the book over and see if you can make heads or tails of it, I'm about ready to put the keyboard through the monitor.

EDIT: just got a little closer, now I need to make it accept a less than argument...

=COUNTIFS(UIC,G2,Year,(TEXT(TODAY()-365,"YYYY")))

prior to the "(TEXT" I need it to "<=" I tried

"<="&(TEXT

but that doesn't work...gonna keep toying with it, if I find a solution, i'll post it up.

Re: Countifs with nested year look up

ok, now it's just being difficult...

=COUNTIFS(Table13[Year],"<" & TEXT(TODAY(),"YYYY"),UIC,G2)

in cell U2 returns a #VALUE error...which makes no sense, as evaluating the formula, it runs smooth all the way through until the end, then dumps the #VALUE after the final computation. I would think it is because it is looking for text on the UIC side and a number on the TODAY side, but using the "TEXT" nested formula, it changes the thing to text instead of a number. I thought that maybe it was that it wants to "<" a text instead of a number, but changing the formula to

=COUNTIFS(Table13[Year],"<" & INT(TEXT(TODAY(),"YYYY")),UIC,G2)

still dumps a #VALUE, probably because now it's evaluating a number on half of the countif(criteria_range1, criteria1) and text on the other half (criteria_range2,criteria2). I know I'm missing something small that is going to make me have a "well, duh!" moment as soon as it's pointed out, but I'm stuck again.

EDIT: before anyone says anything, when I changed it to INT, I also changed the "Year" column to an INT... think I should mention that before going any further, the "Year" column is not a set of numbers, it is a formula that is pulling from the "PHA" column that reads like this :

=TEXT(B2,"YYYY")

I changed it to

=INT(TEXT(B2,"YYYY"))

when I tried the main formula as an integer.

Re: Countifs with nested year look up

forum.ozgrid.com/index.php?attachment/36864/HAHAHA! Got it!

=COUNTIFS(Table13[Year],"<" & YEAR(TODAY()),Table13[UIC],G2)

Changed the year column to

=YEAR(Table13[[#This Row],[PHA]])

now it works perfectly. Knew it was something small lol. I'll post my updated workbook to show the changes. The check sums now match-up across everything on the actual workbook, pay no attention to the blocks of red in the test book.

Re: Countifs with nested year look up

PivotTables are nice, but I never could get the hang of them, and I need it to count all the records from before 2011 collectively by UIC, as PHA's are only good for 1 year. Granted, even these numbers are incorrect, as it does not take the month the PHA was completed into account, but the General wants them sorted a peculiar way, so it ends up that people who had their PHA done in a month other than their birth month put numbers in the wrong place... the powerpoint slide had to be amended with a formula to read negative numbers as 0 because there were months where there are 180 soldiers born that month, and 300 PHAs were completed. The whole thing makes no sense, but Generals do not have to listen to reason...

I think my next step is to modify this further so it takes month into account so that people who had their PHA done in december of 2010 are still good on January 2011.