Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to subtotal this data at each of the xxxx. I know I can go to each cell, but we are trying to create a template because this is just a small sample of the data. Any ideas for a column formula that would work?

    2   6500
    2   6500
    2   6500
    2   6500    xxxx
    3   6500
    3   6500
    3   6500
    3   6500
    3   6500
    3   6500    xxxx
    4   6500    xxxx
    5   6500
    5   6500    xxxx
    6   6500
    6   6500
    6   6500
    6   6500
    6   6500
    6   6500
    6   6500
    6   6500
    6   6500
    6   6500     xxxx
share|improve this question
    
Have you looked at using a pivot table? –  CharlieRB Mar 8 '13 at 16:41

1 Answer 1

up vote 2 down vote accepted

Assuming that your data are in the range A1:B23, this array formula should do the job:

  =IF(A1<>A2,SUM(IF(A1=A$1:A1,$B$1:$B1,0),0),"")

The formula work by first checking whether the current group value (in A1) is equal to the group value in the next cell in column A.

If not, then calculate a sum of column B for all the previous cells in A that match the group value for the current cell. Otherwise, return an empty string.

Since this is an array formula, you will need to use the Control-Shift-Enter key combination to enter it.

share|improve this answer
    
That works great, except I do not want a running subtotal. Sorry, I should have clarified. I need a NEW subtotal for each element in column A. –  Brandon Bearden Mar 8 '13 at 17:26
    
Oops. Will amend my answer. –  chuff Mar 8 '13 at 17:39
    
Maybe I am doing something wrong, but that amended answer does the same thing. –  Brandon Bearden Mar 8 '13 at 17:54
1  
Here's what it looks like in row 10: =IF(A10<>A11,SUM(IF(A10=A$1:A10,$B$1:$B10,0),0),""). I forgot to explain that it's an array formula which you need to enter with the Control-Shift-Enter key combination (and then can copy down)! –  chuff Mar 8 '13 at 18:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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