# Excel question

#### Ross0

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Oaktree

##### MrExcel MVP
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
Use SUMIF

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

this is for the expenses

#### clipro

##### Board Regular
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

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?

##### MrExcel MVP
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
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

Replies
2
Views
64
Replies
0
Views
90
Replies
5
Views
323
Replies
6
Views
428
Replies
0
Views
234

1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

### 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.

### Which adblocker are you using?

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

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