Sum of all the numbers listed above

[abhayxl]

Hi all,

I want to sum all the numbers that are shown above total(row 1243 in the enclosed file).Is there any shortcut to add these numbers by using sum function.Or all these numbers have to manually added up in sum formula.(I want to use sum function and not subtotal).Also when i open the grouping the sum values should remain same.

Thanks

 

[bosco_yip]

Try:

In D1243, enter formula :

=SUBTOTAL(109,D4:D1242)

Regards
Bosco

 

[abhayxl]

Try:

In D1243, enter formula :

=SUBTOTAL(109,D4:D1242)

Regards
Bosco

Hi,
I need sum function only and not subtotal.

Thanks

 

[Hui]

Abhayxl

You should try Bosco's function?

or else try:
=AGGREGATE(9,3,D4:D1242)
Which remarkably gives the same solution as Bosco

If this isn't correct, tell us what the answer is and how you arrived at that

Saying you don't want a function because of it's name is very poor form, especially if you didn't try it

 

[abhayxl]

Abhayxl

You should try Bosco's function?

or else try:
=AGGREGATE(9,3,D4:D1242)
Which remarkably gives the same solution as Bosco

If this isn't correct, tell us what the answer is and how you arrived at that

Saying you don't want a function because of it's name is very poor form, especially if you didn't try it

Hi Hui,

Thanks for the response.The problem with subtotal and aggregate function as suggested by Bosco and you is that when we open the grouping the total just gets doubled. As sum function has been used in the table.

Thanks,
Abhay

 

[Chihiro]

I'd recommend replacing SUM() in groupings with SUBTOTAL().

Subtotal will ignore other subtotal, the function is meant to be used in this type of set up.

 

[Khalid NGO]

Hi abhayxl,

This version of SUMIF should work, it is totally depend on column C blanks:
=SUMIF(C4:C1242,"<>",D4:D1242)

Regards,