Totals

[Cindy]

I have the following scenario:

Group 1 A 100
B 200
C 300
Group 2 A 150
B 250
C 350
etc...

Total A sum(100+150) *using cell ref
B sum(200+250)
C sum(300+350)

Here's the problem. I have many Groups so to get a total
of all the A's is time consuming but doable. However,
when I need to add a group in the middle, it throws off
all my totals and would have to be re-done. Any way
around this?

Any help will be greatly appreciated.

 

[Frank Kabel]

Hi
if column A would conatin the group name for ALL rows try the following
formula:
=SUMPRODUCT(--(A1:A1000="Group 1"),--(B1:B1000="A"),C1:C1000)

or use a pivot table for this

 

[Guest]

A1 title "Group 1" would apply to rows B1, B2 and B3 as
well as C1, C2 and C3. Then in A4 the title "Group 2"
would apply to B4, B5 and B6. B1 and B4 are the same
title as in the example would be "A" then in column C is
the number for A. I need a total of all the A's.

I hope this makes sence.

 

[Frank Kabel]

Hi
and as said: The formulas would be easier (as I showed in my previous
response) if you just insert the group name in EACH row. So in your
example copy 'Group 1' into the cells A2:A3. This kind of dtabase
layout is best to use Excel's formulas and reporting tools like pivot
tables. All other layouts would lead to much more complicated formulas

 

[Guest]

Frank, Thanks for your help. The formula I ended up
using is =SUMPRODUCT(--(B2:B93="a"),C2:C93). I was
looking to find all the A's in Col B and sum the
corresponding # in Col C. I think thats where I got
confused with your answer. As it turns out what is in
Col A doesnt matter. Thanks for the info and sorry for
the confusion.

By the way, what does the -- in the formula do??

Again, thanks for the help.

 

[Frank Kabel]

Hi
in this case use UMIF 8only one condition):
=SUMIF(B2:B93,"a",C2:C93)

 

[Cindy]

Thanks.

-----Original Message-----
Hi
in this case use UMIF 8only one condition):
=SUMIF(B2:B93,"a",C2:C93)

--
Regards
Frank Kabel
Frankfurt, Germany



.