counting occurences of values relating to date range

[Newport Count]

Hi,

i have a spreadsheet that contains a column for each day and i need to input the count from another tab for certain data...example as follows:

column A shows types of fruit - apples, bananas or oranges, row 1 contains the date (todays date, weekdays only) - the count of each fruit needs to be entered in rows 2 (apples), 3 (bananas) & 4 (oranges) for each fruit for each date

i copy and paste a daily report into a new tab in this workbook -Raw Data (the data is always in the same format and the info i need is in column J...."555" = apples, "666" = bananas & 777" = oranges)

i have used the below formula which works:

apples =COUNTIF('Raw Data'!$J$5:$J$65536,"555") etc

however the daily report that is pasted into the Raw Data tab only relates to the current day (date is present in this tab in A1)...how do i get each daily column to only display a count if the date in row 1 matches the date in Raw Data tab A1?

also how do i keep the previous days' data to keep what the count was for that day (instead of counting the current count of the new data only relevent for today)?

much appreciated,
Rich

[zbor]

can you upload example?

[DonkeyOte]

Regard preserving prior data - realistically you will need VBA if you wish to automate the process (and prefer not to keep a log of all historic data).

[Newport Count]

Hi,

please see attached example spreadsheet

- also yes i think it would be better to keep a simple historic record in a seperate tab to record each days data? then that would let me create a formula to show only todays data and previous days whilst showing blank entries for future dates

what would be the best way to do this historical record?

thanks

[Newport Count]

i presume a macro to insert a new line for each day - to insert the date in a cell and insert the rest of the worikings out in another?

[DonkeyOte]

Questions...

When the new daily data is added and old data is overridden is this a manual process or automated in some way ?

What do you want to happen ideally.... for ex. do you want to keep an "all time" sheet for column J ?

Does your "daily" sheet always have the same number of rows ?
Or perhaps put another way... what is the most number of rows that may be returned on any given day in Column J ?

[Newport Count]

Hi,

i need a formula that will count the occurences of a value related to the current date from another tab.

i have two columns that need to be referenced - column E contains types of musical instruments - Trumpet or Tuba or Cornet....column B contains the date that they were bought in the format: 30/12/2008

i need to work out how many instances of each instrument occur in column E for different date ranges - all relating to the current working day

the actual report containing this data is a daily update that is copied and pasted into the same tab every day...so todays report will replace yesterdays report

so i need a formula to tell me how long these items have been in the system -how many at under 30 days, how many over 30 and under 90 days, and how many over 90 days (so the data would change on a daily basis).....so a formula for each type of instrument is required

i do have todays date present on the spreadsheet =TODAY()

i have tried a few COUNTIFs and a SUMPRODUCT but to no avail

thanks in advance for your help

[zbor]

Can you uplaod example?

[DonkeyOte]

Use a Pivot Table (general intro link in sig.) - you can group Date field by Day and set interval to be 30 days etc...

[Newport Count]

attached example - thanks

[DonkeyOte]

This is to all intents and purposes a duplicate of an earlier thread - as a rule do not create duplicates.

On this occasion I am going to merge the two because your post here does at least answer some of the questions asked on your last thread (to which you did not respond).

[Newport Count]

Apologies!

i no longer require the macro and historical data in the first thread i created

all i require is a formula based on the attached example i provided

i need to calculate the occurences related to the current date and how many items are in the system for over 90 working days etc

many thanks