# Using Sumtotal for one variable and total

#### kcaenj

##### Board Regular
I have two columns. Column B is the cash amount for person and column C is the company that they belong to.

I am trying to paste a formula down on Column E that only shows a value when the company number is the last one in the list. So right before the next company starts a total is placed.

THe forumla I am using is one I have used before starting with cell C2:

=IF(C2<>C3,SUMPRODUCT(--(\$C\$2:\$C\$8248=C2,\$B\$2:\$B\$8248)),0)

In the cell where a total is supposed to be it is saying "#VALUE!"

The last row is 8248, that is why I used it in the formula... is this wrong?

What would cause this?

KC

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
kcaenj,

If I hear you right, you have a column of numbers (Company#) that you have sorted and you want to know where there is a change in number.

If so, you would use
=if(C2=C3,"","Last One") in E2 then copy down.

If you are looking to compare company based on something in column B then that's a different story.

The first part is right, I want to recognize where the last one is. The second part would be where there are not equal I want to sum all of amounts in row B where they were the same in row C.

KC

I can't see how to create a solution here with formulas. I could build it with VBA code, but this really is perfect situation for a pivot table.

Here are the steps to build one if you haven't used them before. I am running XP so it might be a little bit different depending on version.

1. select any cell within your data table.
2. From menu bar go Data|Pivot Table
3. Use defaults (Excel list or database and Pivot Table)
4. If the range of your database isn't already filled in, enter it here (\$B\$2:\$C\$8248), better yet, name your database range and enter the range name here. that way when your database grows you only need to update the name reference.
5. Destination, select new worksheet or someplace on an existing sheet.
6. Before clicking on Finish, click Layout. You should see your column headings on right (cash amount and company) Drag Cash Amount to Data and Company to Row and click Ok.
7. Now click Finish.

Pivot tables allow you to slice and dice your data easily and without formulas for code.

One Note, Pivot tables are not updated automatically, you need to right click on the pivot table and choose Refresh Data when your database changes.

kcaenj said:
The first part is right, I want to recognize where the last one is. The second part would be where there are not equal I want to sum all of amounts in row B where they were the same in row C.

KC

Is the data sorted on the company column?

Yeah its sorted by company

kcaenj said:
Yeah its sorted by company

Running a pivot table is btw a good option. By means of a formula, we get:
Book2
BCDE
1AmtCompanySubtotal
224.39comA
338.91comA
428.51comA91.81
511.76comB
635.5comB
721.29comB
811.15comB79.7
921.02comC
1014.17comC
1127.68comC
1225.03comC
1317.46comC
1422.42comC
1514.63comC142.41
16
Sheet1

E2, copied down:

=IF(C2<>C3,SUMIF(\$C\$2:C2,C2,\$B\$2:B2),"")

Always invoke SumIf whenever a single condition prevails.

Replies
1
Views
384
Replies
3
Views
359
Replies
4
Views
157
Replies
5
Views
507
Replies
5
Views
204

1,203,464
Messages
6,055,578
Members
444,800
Latest member
KarenTheManager

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

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