How to add groups based on conditions in Excel?

Question

I have a large set of data where I need to calculate the sum of groups for specific columns in a table. Here is an example of the table:

I need to sum up column H for each date group (column A) such that it will keep display this sum on the last row of the group. In other words, on the first group that we can visibly see here for 3/21/13, we would sum up 38 + 2 + 21 and display 61 to the right of cell H4930. This would be repeated throughout the data so as to display the sum of each group's H column entries at the lowest item in the group as aforenoted.

I am assuming that some VBA will be required here, but I am not sure how to attack this. How would this be done?

Answer 1

I added 2 working columns before your Sum column.

Add: =NOT(ISNA(MATCH(C2,$Q$2:$Q$3,0))) to column N, where the Q2:Q3 are the numbers you wanted to check. Can be hidden.

Add: =IF(N2,COUNTIFS(A3:$A$7416,A2,N3:$N$7416,TRUE)=0,"") to column O. Can be hidden.

Add: =IF(AND(N2,O2),SUMIFS(H2:$H$2,A2:$A$2,A2,N2:$N$2,TRUE),"") to column P. This is your sum.

If you don't actually need to display all the data like this then a pivot table would be a lot clearer. You can filter based on numbers and so on easily.

edit: edited to check for specific codes in column C, per comment. edit2: completely changed per comment. Requiring it to be inline with the last highlighted row is more complex.

Answer 2

Yes as suggested by Idevelop ..you can get this done using pivot table

Select all the data -> goto Insert menu ->Pivot table > say ok

in Row field >Place A column title

in values field >place H column title

that's it

Let me know if you need more details.

Image :