Easy way to sum up the Highlighted Totals

[Rafay Ahmed]

Hi All,
Attached is the sheet, where there are totals of Individual people. What is the EASIEST WAY to sum up all highlighted totals in the given sheet, as I have plenty of data like this.

Thanks

 

[Eloise T]

Are you looking for something other than?

=SUM(C6,C13,C19,C24,C30,C33,C40)

 

[NARAYANK991]

Hi ,

There is really no easy way to sum all the highlighted values , especially if you have a hundred of them.

The correct thing to do would be to always use the SUBTOTAL function , with 9 as its first parameter ; this does the same operation as the SUM function ; the actual benefit of using SUBTOTAL is that every SUBTOTAL ignores nested SUBTOTAL results.

Thus , if you have a 100 SUBTOTAL results in column C , having a SUBTOTAL formula including the entire range , say C2:C41 , will return the correct sum of all the individual figures , ignoring all the SUBTOTAL results that are in between C2 and C41.

Narayan

 

[bosco_yip]

Or……….

In C42, formula across to G42 :

=SUMIF($B2:$B40,"<>",C2:C40)

Regards
Bosco

 

[Rafay Ahmed]

Or……….

In C42, formula across to G42 :

=SUMIF($B2:$B40,"<>",C2:C40)

Regards
Bosco

Thank you so much
This really worked

 

[Rafay Ahmed]

Hi ,

There is really no easy way to sum all the highlighted values , especially if you have a hundred of them.

The correct thing to do would be to always use the SUBTOTAL function , with 9 as its first parameter ; this does the same operation as the SUM function ; the actual benefit of using SUBTOTAL is that every SUBTOTAL ignores nested SUBTOTAL results.

Thus , if you have a 100 SUBTOTAL results in column C , having a SUBTOTAL formula including the entire range , say C2:C41 , will return the correct sum of all the individual figures , ignoring all the SUBTOTAL results that are in between C2 and C41.

Narayan

Thanks NARAYN,
Can you tell me what this "<>" means?

 

[NARAYANK991]

Hi ,

The two signs <> together signify not equal to.

They would read either less than or greater than ; this effectively means everything that is not equal to.

Thus A1 <> 5 means A1 can have values less than 5 and this check will be satisfied , or A1 can have values greater than 5 and again this check will be satisfied.

Narayan

 

[Deepak Sharma]

HI Rafay,
You can also replace SUM Function if you have many on the sheet with SUBTOTAL Function.

For Eg.
=SUM(C2:C42)

with

=SUBTOTAL(9,C2:C42)

If you many to replace, use the FIND and REPLACE feature...

Edit > Replace

Find what: =SUM(

Replace with: =SUBTOTAL(9,

Then apply subtotal at the end of every column to get the values apart from INDIVIDUAL subtotals !!

Thanks !

 

[Eloise T]

Thanks NARAYN,
Can you tell me what this "<>" means?

Note the following for use of SUMTOTAL:

The syntax of the function is:

SUBTOTAL( function_num, ref1, [ref2], ... )

Where the arguments are as follows:


An integer that denotes the calculation type (e.g. sum, average, etc.).
function_num
(See the table below for possible values for function_num).



ref1, [ref2], etc. One or more references to cells containing the values that the calculation is to be performed on.

(Blank cells and cells containing non-numeric values are ignored in the calculation).


REFERENCE: http://www.excelfunctions.net/Excel-Subtotal-Function.html

 

[p45cal]

In the attached is a hotch-potch of the solutions so far with one more user-defined function solution.
If you open the file the list has been filtered by colour and the subtotal is showing in cell C42.
Now clear the autofilter, or remove it altogether and see bosco_yip's solution #2 at cell C43.
At cell C43 there's the user-defined function solution. It uses one of the coloured cells in the list to tell it which colour it should sum.
In the vicinity of I1:K4 are some more examples, some using the colour of the cell above, some using the colour of the cell with the formula in itself.
Stolen from https://www.exceltrick.com/how_to/sum-cells-based-on-background-color/ (Method 2) and there's Chip Pearson's site too where there's a more complex function SumColor at http://www.cpearson.com/excel/colors.aspx