how do I average only certain cells based on month?

J

jcannon1

I am creating a spreadsheet that tracks our monthly goals for sales. My
spreadsheet looks like so:

Jan Feb Mar ETC
5000 5000 5000

I also have a portion for a YTD Average - I want the YTD average column to
pull the average based on the current month - ie if its February I want it to
add Jan + Feb / 2.... if its March I want it to add Jan + Feb + Mar / 3....

Can anybody help me on this?
 
B

Bernard Liengme

If the three-letter months are text, and the first one ("Jan") in in A1
=SUMPRODUCT(--(COLUMN(A1:L1)<=MONTH(TODAY())),A2:L2)

If the three-letter monts are actally dates like 1/Jan/2009, 1/Feb/2009
formatted with "mmm"
=SUMPRODUCT(--(MONTH(A1:L1)<=MONTH(TODAY())),A2:L2)
best wishes
 
J

jcannon

This is getting closer... My data starts in column C1 ( columns A1 and B1
have descriptions of each budget). When I copy your formula into my worksheet
it returns a zero value which I know isn't correct.

I copied the actual data and formula into another worksheet and started at
column A1 and the formula worked.

Unfortunately I can't remove the data I have in columns A & B so is there
another way to state the formula?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top