Subtotal in Data Tab of Excel doesn't group correctly

[GN0001]

Hello to all,

This is Guity,

I have lots of rows (200 K) and 3 columns,

Column A is hours with AM and PM


Column A Column B Column C

09 Am 4 6

10 PM 9 8

12 Am 3 2

09 Am 5 6


Once Hui taught me how to use subtotal in the data tab, but this doesn't work here. The subtotal doesn't group all 09 AM's. It gives me a new total on the change of data on each row of column A. Do I need sort the column A? Any idea?


Your help is precious. G

 

[NARAYANK991]

Hi ,


If you wish to group and sum , use the SUMIF function ; assuming that your data is in columns A , B and C , from cell A2 through cell C5 , then the following formula :


=SUMIF($A$2:$A$5,A2,$B$2:$B$5)


will give you the sum of column B for all 09:00 times. You can also have it as : =SUMIF($A$2:$A$5,"09:00",$B$2:$B$5)


Narayan

 

[GN0001]

Ok, I can use that, it will be hard, because I have to get the unique values first, and then I should use sum if. Why subtotal in the ribbon is not working?

 

[SirJB7]

Hi, GGGGG!

You're right and Hui was right too.

You have to sort your data in the order you want to group it.

The Subtotal button of Data Tab performs the operations you need.

I've just tried your example and it worked fine.

Regards!

 

[GN0001]

Sir JB7

Are you saying that to use the subtotal I have to sort my data and then group it?

Regards,

Guity

 

[Hui]

To use Subtotal you have to sort by the field that you want the subtotals for

Then Apply the Subtotal

 

[SirJB7]

Hi, GGGGG!

Yes, as Hui stated here you must sort the data with the criteria used to apply subtotals. Why? Because Excel subtotal feature work adding a subtotal line each time the criteria change their value comparing two consecutive rows. That's to say if you subtotal by code (column A) and you have the value 31 in rows 2, 34, 35, 56, it will add three subtotals: one after line 2, another after line 35 and the last after line 56. If you sort your data on column A, then you'll have the value 31 in row X, X+1, X+2 and X+3, and the only subtotal will be added after line X+3.

Regards!