Excel question

Ross0

New Member
Joined
Jul 28, 2004
Messages
14
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,996
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

Board Regular
Joined
Jan 12, 2005
Messages
195
Use SUMIF



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

this is for the expenses
 

clipro

Board Regular
Joined
Jan 12, 2005
Messages
195
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

New Member
Joined
Jul 28, 2004
Messages
14

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Either build a pivot table from your data something like...
Book1
ABCD
3Data
4CompanyNameSumofFeeSumofExpenses
5C0153.9414
6C0211141
7C0333.2310
8C0446.5450
9GrandTotal144.61315
Sheet6


or use a set of SumIf formulas...
Book1
ABCDEFGHI
1AssignmentDateCompanyNameFeeExpensesInfoCompanyNameTotalFeeTotalExpenses
2C0117.8185C0144.1549
3C0113188C0218.3158
4C0113.3176C0329.5284
5C0416.8200C0451.4480
6C0310.9105
7C0418.7141
8C0218.3158
9C0415.9139
10C0318.6179
Sheet1


H2, copied across to I2 then down:

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

Ross0

New Member
Joined
Jul 28, 2004
Messages
14
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,307
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top