Calculate % of Grand Total using Microsoft Excel SCAN Function

Welcome to Excel Jet Consult blog post. In this Microsoft Excel video, I demo how to use the new SCAN LAMBDA Helper Function to calculate % of Grand Total. Like | Share | Subscribe


SCAN Function

The SCAN Function scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.


=SCAN ([initial_value], array, lambda(accumulator, value))

The SCAN function syntax has the following arguments and parameters:

  • [initial_value]      Sets the starting value for the accumulator.
  • array      An array to be scanned.
  • lambda      A LAMBDA that is called to scan the array. The LAMBDA takes two parameters:
    • accumulator    The value totaled up and returned as the final result.
    • value     The calculation applied to each element in the array.

Function Application

In this example, we have a simple Sales Records formatted as an Excel Table as seen below

Our goal is to:

  • Extract the unique years
  • Use SUMIFS to calculate Sales Amount
  • Use Scan Function to calculate Percentage of Grand Total


  • In cell J2, execute the formula: =SORT(UNIQUE(Data[Year]))
  • In cell K2, execute the formula: =SUMIFS(Data[Sales Amount],Data[Year],J2)
  • Click Enter and copy down the formula using fill handle
  • In cell L2, execute the formula: =SCAN(K2,SUM($K$2:$K$22),LAMBDA(a,c,a/c))
  • Click Enter and copy down the formula using fill handle

Cell K2 which is the first argument of the SCAN Functions, handles the starting value for the accumulator. Then, we used SUM function and absolute cell reference to aggregate the values in range K2 to K22. Thereafter, we nested LAMBDA function and used a and c as the accumulator and value respectively. Finally, we divided the accumulator by the value which gave us the % of GP as seen in the caption below

To see how to calculate percentage of grand total using PivotTable, kindly watch the video.


See you in the next tutorial


Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *