Excel question

[Ross0]

Hello Excel gurus- I have a quick question that I'm sure can be answered in a matter of seconds.

I have an excel spreadsheet setup with the following colums:
Columns:
A: Assignment Date
B: Company Name
C: Fee
D: Expenses
E: Info

In the bottom row I've created two cells where I've created a function to give me the sum of fees and a sum of the expenses.

My question is I'd like to create a cell function in the bottom of the spreadsheet that gives me totals by individual companies. To do that I would need some sort of sumif function, but can't figure it out.

So for instance if I have 100 entries in the spreadsheet and every 5 entries is from company: "XYZ", how would I create a function to then add only the shopper expenses from column D in the same row as Company XYZ while avoiding the expenses from all the other companies?

I hope that makes sense.

FYI- i'm still working and actively modifying this spreadsheet so I want something that will adapt when I make changes.
Also i'm using Office Excel 2003 SP 1

Thank you!
Jason

 

[Oaktree]

Have you considered a pivot table?

=sumif(b:b,"XYZ",d:d) will get you what you described, but pivot tables are probably better for the multiple iterations which will likely follow.

 

[clipro]

Use SUMIF



=SUMIF(B2:B100;A101;D2:D100)

this is for the expenses

 

[clipro]

I missed sth in A101 is the name of the company that you want to see

Use SUMIF



=SUMIF(B2:B100;A101;D2:D100)

this is for the expenses

 

[Ross0]

him I don't get that, how will that work? Don't i need to define which company I want to get the total expenses for?

 

[Aladin Akyurek]

Either build a pivot table from your data something like...

Book1
	A	B	C	D
3		Data
4	CompanyName	SumofFee	SumofExpenses
5	C01	53.9	414
6	C02	11	141
7	C03	33.2	310
8	C04	46.5	450
9	GrandTotal	144.6	1315
Sheet6

or use a set of SumIf formulas...

Book1
	A	B	C	D	E	F	G	H	I
1	AssignmentDate	CompanyName	Fee	Expenses	Info		CompanyName	TotalFee	TotalExpenses
2		C01	17.8	185			C01	44.1	549
3		C01	13	188			C02	18.3	158
4		C01	13.3	176			C03	29.5	284
5		C04	16.8	200			C04	51.4	480
6		C03	10.9	105
7		C04	18.7	141
8		C02	18.3	158
9		C04	15.9	139
10		C03	18.6	179
Sheet1

H2, copied across to I2 then down:

=SUMIF($B$2:$B$10,$G2,C$2:C$10)

 

[Ross0]

Sounds complicated.

Thanks i'll have to look into that, i'm lost with all those sumifs and functions, but i'll try.

I appreciate the help!

thanks,
Jason