0

I am using Open Office Calc on windows 8.1 Pro

I have the following data already sorted nicely.

    A      B       C
--+-----------------------
1 | Bmw     Gold     £80
2 | Bmw     Silver   £60
3 | Bmw     Blue     £50
4 | Bmw     Green    £20
5 | Merc    Orange   £50
6 | Merc    Red      £10
7 | Ford    Green    £22
8 | Ford    Green    £55
9 | Ford    Blue     £80
10| Ford    Grey     £90
11| Ford    Black    £68

I would like to total each Car Make and put the result in a cell to the right like in the following example

      A      B       C        D       E
--+---------------------------------------------
1 | Bmw     Gold     £80    
2 | Bmw     Silver   £60
3 | Bmw     Blue     £50
4 | Bmw     Green    £20    Total    £210
5 | Merc    Orange   £50 
6 | Merc    Red      £10    Total    £60
7 | Ford    Green    £22
8 | Ford    Green    £55
9 | Ford    Blue     £80
10| Ford    Grey     £90
11| Ford    Black    £68    Total    £315

I can get the totals of each Car Make with formula =SUMIF(C1:C11;"Bmw";A1:A11), and manually put this formula in a cell to the right of each Car Make, adjusting the formula and location everytime it differs.

However if I have 100 different Car Makes in 5000 Rows this would be a timely And error prone task(when selecting the Range's inside SUMIF).

Is there not a menu in Calc which I can apply some sort of formula which is worked out based on a range of data? Or perhaps a better simple way to do it?

Many Thanks

0

In Column D put this formula in the first row:

=IF(A1<>A2;"Total";"")

Then in E1 put:

=IF(A1<>A2;SUMIF(A:A;A1;C:C);"")

And copy down.

enter image description here

  • Thanks, I think your answer would work if OO Calc allowed A:A, C:C shorthand for entire columns, but it does not, so I am working on a neat workaround as we speak – JackKrilly Nov 18 '16 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.