Calculating Totals(sum)... by company

**boris_37** · 04-02-2008, 11:28 AM

So needless to say someone asked me to rearrange some spreadsheet for a custom layout(just for them). Now i have 3 columns of numbers that i need to subtotal for each company

laid out kinda like this

Company A 45 67 87
Company A 83 0 23
Company A 58 45 0 Subtotal=

Company B 5 47 57
Company B 13 11 26
Company B 24 0 59 Subtotal=

Now i know i can do a sum(B134:G334) type function just block select the values. However if i have 50 companies the formula will always be only 3 tall. Sometimes i may need it to be 10 row of values or 1 row.

So does anyone know how to make it total all of the values for a specified company?

**NBVC** · 04-02-2008, 11:51 AM

Have a look at the Sumif function... see if you can use it.

**oldchippy** · 04-02-2008, 11:52 AM

Hi,

Sorry about the last one, but try this in L1

=SUMPRODUCT(--($A$1:$A$150=A1)*(--($B$1:$K$150)))

Then this in L2

=IF(A2=A1,"",SUMPRODUCT(--($A$1:$A$150=A2)*(--($B$1:$K$150)))) copy this one down the column

**boris_37** · 04-02-2008, 12:00 PM

Yeah i guess that works but how would i get the "criteria" selection to be "on same line as Company A.

It is instead supposed to be a number value example ">65000"

I dunno this seems more like programming than excel...

L1 L2?

What are these. and my god, what a function gimme a little bit to try and figure it out.

**oldchippy** · 04-03-2008, 05:07 AM

Hi,

You say there are 50 companies, 3 rows deep + 1 blank, so the range covers 200 rows.

I've amended the formula's so the one in L1 and M1 can be auto-filled down the columns and the totals will appear on the bottom row as your example.

**boris_37** · 04-03-2008, 12:31 PM

okay hmm it sorta works..

K i said rows of data so like...

Company A
Company A

Company B

Company C
Company C
Company C etc etc etc

So you have it right but kinda the opposite of what i want. I tried to modify the forumula... to ignore the headings etc but it doesn't want to work.

=IF(A3=A4,"",IF(A3="","",SUMPRODUCT(--($A$2:$A$200=A3)*(--($D$2:$G$500)))))

A is still the companies and the values are held in DEFG i need a total after them so yeah... sorry if i was unclear between columns and rows? thought i was but yeah i can see its easy to mix up the two.

But yeah anyway you can see why its not working?

I've provided the spreadsheet that is a.. pretty much exact example of what it is i'm doing(modified since i cant give real data.. lol). I've shown how i was doing it and what the info will look like. Should make it 10x easier for you, sorry i didn't think of this before.

**oldchippy** · 04-03-2008, 12:47 PM

Hi,

Try this, seem to work OK, adjust the ranges to suit your data

**boris_37** · 04-03-2008, 04:25 PM

yup it's working. Actually with this i found a problem in my original. I had 2 companies with very similar names and i had added their totals together. With this it actually caught the problem(i thought the formula whacked out) so yeah i just wanna thank you. It is good to have a formula just i couldave never figured that out. I understand how its done, but yeah.. .hmm lol

Thanks again. Hopefully i will have more one day to put your expertise to work, its great.