Creating a category (text) with a number value and summing it

M3G4L0D0N

New member
Joined
Oct 18, 2012
Messages
3
Reaction score
0
Points
0
Hi everyone,

I have two sheets that look like:


Sheet1
ABC
1DateAmountCategory
218/10/12€ 15,00Food
319/10/12€ 40,00Car
419/10/12 € 15,00Tools
5 20/10/12€ 5,00Food

Sheet2
ABC
1MonthAmountCategory
2Oct-12€ 20,00Food
3Oct-12€ 40,00Car
4Oct-12€ 15,00Tools

I'm trying to make cells B2 to B4 return the total of each category (B2+B5 in sheet1 = B2 in sheet2, in the example above).
Since the list is very large and will continue growing, I need to find a way to automate this calculation.
The way I've tried to approach this is:

Based on SUMIF( range, criteria, [sum_range] )
I'm trying =SUMIF((Sheet1 column C), (Food), B:B)


where "Food" is a name defined for each category, but I don't know how I could match automatically cells B2 and B5 to C2 and C5.
I simply need Excel to find and sum all of the expenses within a category, in a selected month.

Any help or alternative ideas will be greatly appreciated.

Thanks.

 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Put this formula in B2 Sheet2

=SUMIF(Sheet1!$C$2:$C$5,"Food",Sheet1!$B$2:$B$5)

copy down and change name of criteria (Food) in B3 and B4
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
You could just replace "Food" in that formula to C2

=SUMIF(Sheet1!$C$2:$C$5,C2,Sheet1!$B$2:$B$5)

Then when you copy it down it will automatically look for the value in column C of the same row.
 

M3G4L0D0N

New member
Joined
Oct 18, 2012
Messages
3
Reaction score
0
Points
0
Looking good!

Thanks for the fast and effective reply. Both formulas worked, although I preferred the second one. I will just point out that since I'm using Excel 2010, I had to replace the commas with semicolons. This is the current formula for the cells in column C:

SUMIF(Sheet1!$C$2:$C$5;C2;Sheet1!$B$2:$B$5)

But I'd like to take it one step further. The idea is to select the date range and get the sum and categories for that period.
So if I select the cells A3 to A6 on Table1 below, column B on Table 2 would display the total spent in each category.

Table1
ABC
1DateAmountCategory
230/09/1215€Phone
302/10/1218 €Food
405/10/1240 €Car
509/10/125€Office
618/10/1220 €Food
701/11/1230 €Movies


Table2 (displaying only expenses in October)
ABC
1MonthAmountCategory
202/10/1238 €Food
318/10/1240 €Car
45 €Office

Some notes about these tables:
  1. In Table1 entries are sorted from oldest to newest, but in Table2, sorting doesn't matter.
  2. The 'Month' column would contain the earliest and latest date from the selected month.
  3. There would be a new table for each month.
  4. Columns B and C on Table2 should be automatically filled once selected the date range.
This is what I think the formula should do, but I don't know how to "translate" it:
SUMIF((I select A3:A6 on Table1);(Excel chooses one category within the date range);(Sums values for selected category))

Any ideas how to do this?

Thanks.
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
I can help with the sorting it out by month, I am unsure of how to display the first entry of the month and the last.

If you add a new column on Sheet1, Column D, and just label it as month.
You can parse out the month from the date you have in column A by using this formula. =MONTH(A2)
that will just give you the number value of the month from column A.

On Sheet2 you will need to add another column, Column D, I labeled it as month to check. then in cell D2 just put the month you want in your example 10.
Your formula in B2 should now look like. =SUMIFS(Sheet1!$B$2:$B$13,Sheet1!$C$2:$C$13,C2,Sheet1!$D$2:$D$13,$D$2) replace the , with ;
Then you can just change the month to search for in cell D2.

As a side note, you may need to tweak the dates to return the correct values. I had to enter the date as month/day/year on my end to get my functions to work.
If you are using a European version that has day/month/year it may be ok.

I have attached a file showing this working.
View attachment Megalodon.xlsx

Simi
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Here is one way you could fill in the month on Sheet2. I just updated Simi's file
 

Attachments

  • Megalodon_Updated.xlsx
    11.3 KB · Views: 11

M3G4L0D0N

New member
Joined
Oct 18, 2012
Messages
3
Reaction score
0
Points
0
Amazing! So kind of you guys. Works like a charm. :cool:
 
Top