Auto Populate at the end of the month

**t_finn** · 01-12-2009, 01:06 PM

I have 3 columns of data 1. items (numerical numbers from 1-15) 2. status(red, yellow, green) 3.cost(dollar amounts). out of these 3 columns 2. status changes every month. i need to keep a running tab on how many of items have a red, yellow, or green status at the end of each month and how much cost it is per status state. at the end of each month i could do a manual count an populate the reults manually, but how would i automate it so i will have the result from feb added to the reults from jan which were added to the results from dec, each in a different column based on month?

**NBVC** · 01-12-2009, 01:23 PM

I'm not sure I get it. You just want a cumulative total to appear in the bottom table?

i.e. =SUM(F23,G22) in G23, copied across.

If no, please elaborate... maybe fill in the expected results in your sample workbook.

**t_finn** · 01-12-2009, 02:06 PM

no, what i am expecting to see are the two tables there.
example in jan. there might be 5 items with a status of red, 5 with yelow amd 5 with green. Then in feb there will be 6 items with a status of red, 4 with yellow and 5 with green. so what i would want is for excel to create a table across the top with the months (jan - dec) along the side the status and populate those status totals for each month. so i would have a colum titled jan and under that would be the number of items for status red, then yellow then green, then at the end of feb it will do he same thing, it will add a new column called feb and then place the new totals for the month.

**NBVC** · 01-12-2009, 02:38 PM

So how would you identify which month(s) each record in the Raw data applies to?

**t_finn** · 01-12-2009, 04:22 PM

ummm, i dont know.

i guess i was looking for the code to do this

when today()=1/31/2009
countif(a1:a100,"YES").............................................prints out #( example 3)

When today()=1/31/2009
countif(a1:a100,"no").................................................prints out #(eg. 5)

when today()=2/28/2009
countif(a1:a100,"yes").............................................prints out 10

when today()=2/28/2009
countif(a1:a100,"no)................................................prints out 4

and it would look like the attachment.

**NBVC** · 01-12-2009, 04:44 PM

the Today() function is volatile and recalculates to show the current date always... it does not allow you to keep previous dates... you would need VBA code to do that...