×

Calculate Quarter & FY Totals With Single Excel Function

By Chris Newman •  Updated: 03/20/24 •  4 min read
How to create multiline text in ribbon tooltips with Excel.

Excel Function To Provide Quarter and Full Year Totals

I've been on a custom function blitz ever since I learned how to use Lambda Functions in Excel. I'm super excited to share with you a function that I created to replace the manual work I do all the time as a finance professional.

I have created a LAMBDA function called QTRFY() that when fed 12 month's worth of data, will automatically perform the task of calculating the totals for the quarter and full year (FY).

Take a look at this function in action!

QTRFY Excel Function

Formula Logic With Dynamic Arrays

Let's first walk through the logic of how we can form an array output that both calculates quarter and full-year totals based on a range. Below is the formula I used (I challenge you to write something shorter!).

=HSTACK(BYCOL(WRAPCOLS(A2:L2,3),SUM),SUM(A2:L2))

WRAPCOLS Function

The WRAPCOLS function is being used to reorganize the single row of data into a 3-row by 4-column spill range. Said another way, I am wrapping the range of cells A2:L2 after every 3 values, creating a 3x4 matrix. This essentially groups the data into quarters for me.

WRAPCOLS Function

BYCOL Function

After the WRAPCOLS function has rearranged the data, BYCOL is used to process this data column by column. Specifically, BYCOL takes the array output from WRAPCOLS and applies the SUM function to each column individually. This results in an array where each element is the sum of one of the newly formed columns from the WRAPCOLS function. This effectively creates totals for each of the quarters.

BYCOL Function

HSTACK Function

Finally, the HSTACK function is used to horizontally combine (stack) arrays or values. In the previous sections, we used BYCOL + WRAPCOLS to calculate the Quarter totals in an array output.

We now need to add a full-year calculation using the SUM function to the Quarterly output so we have an array output with both calculations.

As you can see in the illustration below, feeding both the Quarters and the Full Year formulas through HSTACK will combine them into a single array result.

HSTACK Function

Converting To A LAMBDA Function

Now that the logic is working just the way we need it, it's time to turn this into a reusable custom function. We can now utilize the LAMBDA function to do this.

Writing The LAMBDA On The Grid

When I'm building out LAMBDA functions, I like to first ensure they work on the grid (spreadsheet). To do this, we'll need to take 3 steps:

  1. Write the LAMDBA formula in a cell
  2. Pass values into LAMBDA function
  3. Add An Error Handler

Step 1: Converting our base formula logic to a LAMDA is pretty straightforward. We'll just replace the range A2:L2 with a variable called "mths".

=LAMBDA(mths, HSTACK(BYCOL(WRAPCOLS(mths, 3), SUM), SUM(mths)))

Step 2: Next, we'll need to give the "mths" variable a value to see the output of the LAMBDA on the spreadsheet:

=LAMBDA(mths, HSTACK(BYCOL(WRAPCOLS(mths, 3), SUM), SUM(mths))) (A2:L2)

Step 3: Finally, I wanted to get a little fancy and add an error handler to this function to ensure a range of 12 cells is actually getting input into the function. I decided to handle this with an IF function that passes the range through a COLUMNS function. If the test determines the range is not equal to exactly 12 cells, then I output an error message through the function.

=LAMBDA(mths, IF(COLUMNS(mths)<>12, "#INVALID - Range must be 12 cells", HSTACK( BYCOL( WRAPCOLS(mths, 3), SUM), SUM(mths)))) (A2:J2)

Embedding The LAMBDA Function Into The Workbook

Now to create the custom function. To do this, all we need to do is create a Named Range.

  1. Navigate to Formula Tab >> Define Names Button
  2. Enter a Name (eg QTRFY)
  3. Enter a description in the Comments (this will appear as a tooltip in the Formula Bar)
  4. Use the Refers To section to paste in your Lambda formula
  5. Click OK
Create Named Range For Lambda Function

After you have created your Named Range, your custom function will appear when you start typing the function's name in the Formula Bar.

Calling Lambda Function In Formula Bar

Note: This will only be stored in this single workbook. If you want to import this LAMBDA function to other spreadsheet files, you will need to set up a LAMBDA importing process.


I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can use a LAMBDA function in combination with various other Dynamic Array functions to create your very own Quarter/Full Year Total calculator. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.