Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .