Using Sumtotal for one variable and total

[kcaenj]

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

 

[yankee428]

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.

 

[kcaenj]

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

 

[yankee428]

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.

 

[Aladin Akyurek]

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?

 

[kcaenj]

Yeah its sorted by company

 

[Aladin Akyurek]

Yeah its sorted by company

Running a pivot table is btw a good option. By means of a formula, we get:

Book2
	B	C	D	E
1	Amt	Company		Subtotal
2	24.39	comA
3	38.91	comA
4	28.51	comA		91.81
5	11.76	comB
6	35.5	comB
7	21.29	comB
8	11.15	comB		79.7
9	21.02	comC
10	14.17	comC
11	27.68	comC
12	25.03	comC
13	17.46	comC
14	22.42	comC
15	14.63	comC		142.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.