Excel: Add Data Based On Month

Mad catz Lumicon controller (ps2)
March 31, 2010 at 09:07:27
Specs: Windows Vista
I have the following formula im having trouble with Im hoping someone can help me out

A B D
Date 02/30/2010 03/31/2010
Pop $23.00 $42.00
Candy $15.00 $12.00
Fuel $37.00 $25.00

there would be one colum created for each day, and I would need a formula that says look up each column for the month of March and add the candy.

Then look up each column for the month of february and add the candy


Any help would be appreciated


See More: Excel: Add Data Based On Month

Report •

#1
March 31, 2010 at 10:37:31
Would it not be simpler if you reversed it:

Date	Pop	Candy	Fuel
			
3/26/2010	 $179.80 	 $1.75 	 $314.65 
3/27/2010	 $193.70 	 $1.85 	 $358.35 
3/28/2010	 $89.10 	 $1.98 	 $176.42 
3/29/2010	 $94.30 	 $1.87 	 $176.34 
3/30/2010	 $139.23 	 $1.75 	 $152.40 
3/31/2010	 $84.20 	 $1.81 	 $152.40 
			
March Total	 $780.33 	 $11.01 	 $1,330.56 
			
4/1/2010	 $179.80 	 $1.75 	 $314.65 
4/2/2010	 $193.70 	 $1.85 	 $358.35 
4/3/2010	 $89.10 	 $1.98 	 $176.42 
4/4/2010	 $94.30 	 $1.87 	 $176.34 

Then you can total across and get total of all expenditures for March.

You can see each months total individually and you can simply "total the totals" for your yearly figures.

You can of course do the same thing going across the columns,
simply add a totals column at the end of every month, but that would be 365 daily columns, plus an additional 12 monthly columns and probably a yearly column.

That's an awful lot of columns to scroll over to.
I think doing in down rows makes for a simpler solution.


MIKE

http://www.skeptic.com/


Report •

#2
March 31, 2010 at 10:49:41
Mike

Thanks for responding, I could reverse it, however the data that the customer enters, (the dollar amounts) will continue everyday for years,

I cant put a monthly total row in, cause i would have to do this every month for them, it kind of needs to auto calulate on another page based on the month specified..

For axample page 1 had a date field where the customer inputs a month, and that is used to grab all the totals for pop for that given month and places it in a field on page 1. Later in the day they may need to pick a different month and the field needs to change.

If that makes sence

Bob


Report •

#3
March 31, 2010 at 11:27:02
will continue everyday for years,

Excel has a 256-column limitation but it has 65536 rows.

I don't think you can fit a whole year going column wise.

Going rows wise you can keep adding data for over 150 years.


Excel 2007 has bumped up these figures to 16,385 columns and 1,048,576 rows.
So 2007 gives you more breathing room.


I'm sorry I don't understand what your doing.
A bit more detail in your explaination might help.
Just exactly how is it set up?
Cell Numbers/Locations.....

You can post examples using the pre tags which appear at the top of the input window next to Comments:

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 31, 2010 at 11:34:10
Mike

I took your advice.I am switcing the posting page to go the other direction and I think a Vlookup formula will work

Thanks again for your help. your right it made more sence to switch it

Thanks

Bob


Report •

#5
March 31, 2010 at 12:29:06
Mike

I switched everything as your example in the earlier post

So using your example,

I have a field lets call it A1 on worksheet 1 that I will place a date into for example 3-27/2010

In A2 on worksheet 1 I would like it to look up the matching date on worksheet 2 and fill in the amount for pop

In A3 on worksheet 1, I would like it to look up the matching date on worksheet 2 and fill in the amount for candy

Finaly in A4 I would like it to fill in an amount for all items sold in march of 2010

I think I can use Vlookup but im not 100% sure

I might also be able to use a sumif, but again not 100% sure

Any help you could offer would be appreciated

Thanks

Bob


Report •

#6
March 31, 2010 at 19:59:28
What are you doing?

example 3-27/2010 is not a valid date unless you have custom formatted it.

In A2 on worksheet 1 I would like it to look up the matching date on worksheet 2 and fill in the amount for pop

Where is the information on worksheet 2 coming from?

In my example all I have used are simple =SUM() formulas to total up the month.

3/26/2010	$179.80 	$1.75 	     $314.65 
3/27/2010	$193.70 	$1.85 	     $358.35 
3/28/2010	$89.10 	        $1.98 	     $176.42 
3/29/2010	$94.30 	        $1.87 	     $176.34 
3/30/2010	$139.23 	$1.75 	     $152.40 
3/31/2010	$84.20 	        $1.81 	     $152.40 
			
March Total	$780.33 	$11.01 	     $1,330.56 
	     =SUM(B1:B6)	=SUM(C1:C6)	=SUM(D1:D6)

You'll have to go into more detail about what is happening.

MIKE

http://www.skeptic.com/


Report •

Ask Question