Include Data 90 days from Current Date Only

[martialtiger]

Hey Everyone,

I am creating a spreadsheet where I track data on a 90 day rollin
basis. Here is the data I track "A,4+,T". How can I count the tota
numbers of each data inputed in a column for the past 90 days only fro
the current date? This means that each passing days, there will b
times when data is dropped. I hope this makes sense to you.

I am using Excel 2002.

TIA ,



Jona

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100>=TODAY()-90),(B1:B100<=TODAY()))

where your criteria are in a1:a100 and date are in b1:b100

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

I think I didn't clearly explain what I'm looking for. Let me tr
again.

In my workbook, I have 2 sheets.

1st sheet will contain the data and is where I input data.
Column A contains the date, column b is a validation lis
(blank,"A","4+","T").

Now on the 2nd sheet I merely want to calculate how many time
"A","4+", and/or "T" is inputted. But the catch to this is that I wan
the formula to only calculate 90 days worth of entry from the curren
day backwards. On another cell, I want to show the date for when th
last data was inputted.

Hope this clears it all up.

Thanks for all the help!

Jona

 

[martialtiger]

Is there anyone who could figure this out? Or can someone point me ou
in the right direction....

Thanks,

Jona

 

[Bob Phillips]

Doesn't my formula provide the first of these?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

Bob,

I tried your formula but for some reason it doesn't work. :

 

[Bob Phillips]

Ooops, missed a double unary

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100>=TODAY()-90),--(B1:B100<=TODAY()))

=SUMPRODUCT(--(A1:A100="4+"),--(B1:B100>=TODAY()-90),--(B1:B100<=TODAY()))

=SUMPRODUCT(--(A1:A100="T"),--(B1:B100>=TODAY()-90),--(B1:B100<=TODAY()))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

Thanks Bob,

It's coming together. Is there anyway to add all the data together?
Also, do you know how I can display the date of when the last entry wa
made? I had one that worked, but the data was entered across rows.
The data is now entered in a column and the same formula doesn't wor
for some reason.

Here's the old formula:


{=IF(COUNTA(A1:A100)=0,"",MAX((B1:B100)*(A1:A1OO<>"")))

 

[Bob Phillips]

Does not + work? That is

=SP(...)+SP(...)+SP(...)

The latest can be got with

=SUMPRODUCT(MAX((A1:A20<>"")*(B1:B20)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

Bob,

The date of the last entry does not work. I want to have a cell tha
gives the last date that the entry was made

 

[Bob Phillips]

It does work, just tried it again.

What does your data look like, and what do you get.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

Here's how I have it.

2 sheets

1st sheet is my summary sheet

2nd sheet is data:

Row 1 contains agent names from B thru M
Column A contains the date from A2 to 368
Then each column contains either a blank, "A", "4+" or "T" for eac
agent.

Now in my summary sheet I have the agent names down column A and i
column B I tally the total "A's", column C I tally the total "4+'s" an
column D I tally the total "T's" for each agent. Now in column E
want to have the last date that an "A" or "4+" or "T" was inputted fo
each agent.

:confused

 

[martialtiger]

I'm realizing the error comes from the fact that I have a formul
inputted down my date column that automatically updates by adding th
current date to the next row. Is there a work around this

 

[Bob Phillips]

Did you adapt the formula to that data?

=SUMPRODUCT(MAX((B1:B20<>"")*(A1:A20)))

Does your date formula look like

=A2+1

etc.

If so, that shouldn't make any difference.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

Yes. I adapted the formula to the correct column for the dates. Thi
is the formula I have in the dates' column.


Code
-------------------
=IF(A2<TODAY(),A2+1,""
-------------------


And this is the actual formula I am using to try and retrieve the las
date.


Code

 

[Bob Phillips]

Try a different formula

=INDEX(A2:A367,MAX(ROW(2:367)*(NOT(ISBLANK(B2:B367)))))

still an array formula, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

Bob,

We're almost there. Now I'm getting an error with the date showing i
I entered data for today. It just goes blank in the cell.

Any idea? :rolleyes

 

[Bob Phillips]

=IF(COUNTA(B2:B367)=0,"",INDEX(A2:A367,MAX(ROW(2:367)*(NOT(ISBLANK(B2:B367))
))))

still array formula

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[martialtiger]

Bob,

It doesn't seem to fix the problem. When I entered data today, i
shows up blank in the cell displaying last date. For data I entere
yesterday (July 11), it is showing up at (July 12). And, I am enterin
it as an array, just so you know

 

[martialtiger]

I think I solved the issue. This is the final formula I used.


Code