COUNTIF? Maybe?

[Sanven]

OK... so I'm lost and not 100% sure where to look, and then I came across this site... it's a godsend.. THANK YOU in advance...

OK, so here's what I'm trying to do..

The first column will have a date, another column will have either a 1 or a 2, (the 2 being the one I need)... what I need the formula to do is look at column A, and based on the date, either 30/60/or 90 days from the date in the column, to count the total number of 2's within that date range.. There are 3 return cells (one for 30, one for 60 and one for 90), but I'm totally stimied as to how I would accomplish this..

I can do most of the basic excel forumas, but this one is way over my head..

Thank you again!

 

[Jonmo1]

Welcome to the board...

Are you looking for the range of 30/60/90 days in the PAST or FUTURE, based on Today's Date?

Try
For dates within 30 days in the PAST from today
=SUMPRODUCT(--(A1:A100 < =TODAY()),--(A1:A100 > =TODAY()-30),--(B1:B100=2))

For dates within 30 days in the FUTURE from today
=SUMPRODUCT(--(A1:A100 > =TODAY()),--(A1:A100 < =TODAY()+30),--(B1:B100=2))

Hope that helps.

 

[Sanven]

Thanks for the super fast response, that one doesn't appear to be working (though I'm sure it's operator error - this is really the first complicated formula I've been asked for in years...)

Basically

A8:A108 are the dates
D8:D108 is the 1/2 switch, though I'm only concerned with the 2

The dates would be in the future as it's a sales forcast...



Don't know if that would change things up?

 

[Jonmo1]

OK, so the dates are future...

Try

=SUMPRODUCT(--(A8:A108 > =TODAY()),--(A8:A108 < =TODAY()+30),--(D8:D108=2))

If that doesn't work, it's likely that your dates are not really dates, but numbers stored as text.

Also, make sure to remove the spaces around the > and < symbols
I put those there because the board software sometimes thinks those symbols are HTML code and it cuts off the formula..

 

[Sanven]

I think that's got it..

TY soo much! Saved me a fist full of asprin and a fifth of Jack later this afternoon.

Have a great one!