# Excel Formula for conditional subtotals

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
``````
-
Have you looked at using a pivot table? – CharlieRB Mar 8 '13 at 16:41

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.

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