Dynamic IF Depending on Date.

[Birdster]

I hope someone can help...

I log each time an asset is passed by me. In column order, it is Date/Call Number/Location/Asset Number. A simple "=CountIF($D$2:$D$10000,Dx) x being whichever line.

Works great and has for a year. But now I need to add a column adjacent that does the same thing except only go back as far as one year from the date entered into column "A" of that line. This would give me a view of the total times the asset has been sent to me and how many time in the year of the date logged.

I've hit a snag as I cant get "IF" to go back only 365 days of column "A". I either get an answer of 0 or the total from the column if not an Error.

Can anyone get me pass this block?

Much appreciated for anyone assistance.

[JBeaucaire]

How about:

=COUNTIF($D$2:$D$10000, ">=" & Dx-365)

[Birdster]

Column D has the "Asset Information" and Column A has the date logged. I need it to count column D IF the date in column A is less than 365 days old. Subtracting 365 from column D would be inaccurate. should I attach a sample of my file?

Oh and thanks for the reply!

[JBeaucaire]

Perhaps:

=SUMPRODUCT(--($A$2:$A$10000>=TODAY()-365), --($D$2:$D$10000=Dx))

[Birdster]

WOW! I think that did it. Using "Today()" rather than trying to subtract from the "A" column. This will be accurate on the day I enter it but can't be used for historical values as it would change when the "Today()" changes. But that is not so important as knowing the day I enter the information and know the last years history of the asset. Works for me! Cheers JB

Thanks for your support and prompt replies.

[JBeaucaire]

You can replace the TODAY() reference with another fixed cell reference like M1 where you enter the date you want to be the END of the year range. Then you can put today's date or any date in cell M1.

=SUMPRODUCT(--($A$2:$A$10000>=$M$1-365), --($D$2:$D$10000=Dx))