Est. 2002
SUMIF Excel across multiple sheets is a handy formula to know if you want to summarise date in Microsoft Excel. This tutorial will not only show you how to use SUMIF with many sheets, it will break down the formula showing you exactly how it works. This way you can use this formula with other similar formulas such as AVERAGEIF or COUNTIF.
What this tutorial covers:
Many people prefer an in person training course. The fact that you can ask questions and get immediate answers is invaluable and something that can't be replaced by pre-recorded tutorial videos. Our trust pilot reviews will tell you why our Excel 365 intermediate training course run in London and other areas is just the thing you need. You can even choose to take the course online with a live instructor. As we believe there is no substite for live online training.
By using SUMIF Excel allows you to easily add numbers across multiple sheets. So if you're looking for a way to add, or sum, figures that are spread over different worksheets then you've come to the right place. The job of the SUMIF function is to add, or sum, a column of numbers depending on a certain criteria. That's where the if part of the function comes in. Have a look at the following function:
=SUMIF(A:A,D7,B:B)
The above SUMIF function checks column A to see if the value in cell D7 appears. If so, the SUMIF function will then add up the value, in the same row, in column B:B. Let's have a look at a practical example.
With the above example we want to calculate how much money we spent on Consumables. Once we've done that, we can then use the Auto-fill function to copy the formula down for all the other expenses.
The SUMIF Excel function looks down column F (F:F), looking for the word "Consumables". The first time the SUMIF function comes across the word "Consumables" is in cell F3. Then, SUMIF, adds the £-36.00 in cell h3.
=SUMIF(Range,Criteria,Sum_Range)
After that, SUMIF continues down until cell F14. Because this cell also contains the text "Consumables", SUMIF adds the value in cell H14 to the £-36.00. In effect, the SUMIF function adds both the numbers together. Now, SUMIF will return the value £-40.00. The SUMIF function then continues down the column doing the same for the entire column. The total of which is £-702.00.
So, hopefully you have an understanding of how the SUMIF Excel function works. There is one, tiny, problem. That is SUMIF only works when the data is on one sheet. If you wanted to look at information across multiple sheets in Excel then you have to adapt the formula a little.
To get SUMIF to work across multiple sheets you'll make use of the INDIRECT, SUMPRODUCT, Absolute References and of course the SUMIF function. So let's get started.
If you are simply looking for SUMIF Excel function that goes with the above video tutorial; here it is:
=SUMPRODUCT(SUMIF(INDIRECT("'" & $D$5:$D$16 & "'!F:F"),Summary!A8,INDIRECT("'" & Summary!$D$5:$D$16 & "'!H:H")))
You can use the above formula to SUMIF across multiple worksheets by copying and pasting into cell B8 of the SUMIF across multiple sheets starting exercise file.
Now I bet you're wondering how this SUMIF multiple sheets formula works. There are 3 functions that are involved to get this working. The SUMPRODUCT, INDIRECT and SUMIF.
You already know what SUMIF does (see above) but what do the other functions in this formula do?
Using SUMIF in Excel across multiple sheets involves using the INDIRECT function, in this context, the INDIRECT function references the names of each month. The names of these months are in cells D5 to D16. INDIRECT uses those names in cells D5 to D16 to reference the sheets across the bottom. - See Video Tutorial
Therefore, to get an understanding of how INDIRECT works with SUM have a look at the following example:
If you wanted to SUM all the expenses on the Apr sheet, instead of writing:
=SUM(Apr!H:H)
As a formula, you could write.
=SUM(INDIRECT(A1&"!H:H"))
In the above formula, you must understand that cell A1 contains "Apr", which is the same as the sheet name at the bottom.
When the INDIRECT function is used alongside the SUMIF Excel function something interesting happens. The SUMIF function loops through all the months in the list so take the following sheet. In other words the SUMIF function, with the aid of INDIRECT looks at ranges across sheets Jan to Dec.
Just so you know, I'll put my formula in cell B1, but I want the SUMIF to get the data from column H:H for all of the months. If you can do the same and enter the following formula in cell B1.
=SUMIF(INDIRECT($A$1:$A$12 & "!F:F"),"*",INDIRECT($A$1:$A$12 & "!H:H"))
Now, if you tried the above formula in a simple SUM function, it wouldn't work. You have to use SUMIF. Also, note the asterisk (*) for the criteria argument. This allows the SUMIF function to look at all of the criteria. This is known as a "wild card". You could just have easily entered: "Consumables" instead. This would make the SUMIF function look for Consumables.
The above formula will result in the following:
You can see that the SUMIF function has "spilled" down 12 rows.
If we, now, wanted to add up all these numbers together we can use the SUMPRODUCT function.
The SUMPRODUCT number does exactly what it's name describes it to be. It sums the product of numbers within it. Using the SUMPRODUCT function wrapped around the SUMIF and INDIRECT functions is essential if we want to to get the result the want.
By the way, just so you know, product of a number is the value you get when you multiple them together.
An example of how the SUMPRODUCT function works can be see from the following sheet:
If you want to SUM the product of the numbers, you cannot use the following formula.
=SUM(A2:A14)*SUM(B2:B14)
The result would be £510,526.15 which is obviously totally inaccurate. The formula process should be:
A2 * B2 + A3 * B3 + A4 * B4.....
The SUMPRODUCT function allows us to calculate this without having to write out the entire function. If you didn't use the SUMPRODUCT function you could only calculate the total this way:
Now that you are armed with the SUMPRODUCT function you can do use the following formula:
=SUMPRODUCT(A2:A14*B2:B14)
to do the same.
SUMIF Excel across multiple sheets has a few functions that you need to get your head around. However, now it's time to bring this all together into one amazing formula. To make it easy, I've created step by step instructions:
First, if you haven't already done so, download the SUMIF across multiple sheets exercise file.
Click in cell B8.
Enter the first part of the formula:
=SUMPRODUCT(SUMIF(INDIRECT(
Now, using the mouse select the months in cells D5 down to D16.
Next, press F4 on the keyboard to fix the references. You will see the dollar ($) signs.
After that, continue the formula by add the bit below in bold:
=SUMPRODUCT(SUMIF(INDIRECT($D$5:$D$16 & "!F:F")
This concatenates (joins together) the month name of the referred sheet to column F on that particular sheet.
Continuing on, enter a comma and then click on cell A8 on the Summary sheet for the criteria of the SUMIF function.
Now, enter another comma and the second INDIRECT function for the sum range. I've put the code you should add in bold.
=SUMPRODUCT(SUMIF(INDIRECT($D$5:$D$16 & "!F:F"),A8,INDIRECT($D$5:$D$16 & "!H:H")))
Press Enter and view the glory of the result:
Now all you need to do is to auto-fill the formula down and voilà.
Nearly done! Now it's time to tackle the profit that we made each month.
We can use the INDIRECT function to calculate how much profit we made each month. Do the following:
Click in cell E5.
Enter the following formula:
=SUM(INDIRECT(D5&"!H:I"))
Press Ctrl + Enter (Handy trick for keeping the focus on the same cell).
Now, using the right mouse button, drag down to cell E16, the month of March.
When you let go choose - Fill without formatting.
Now you can sit back, relax, and bask in the splendour of how you have just made Excel work for you. You've definitely taken a step towards being the office Excel Guru.
There is one addition that should be made to the formula. The formula that's been presented above uses an example with no spaces in the sheet names. That being the case should your sheets be called something like "April 2022" then the formula would need to be adjusted.
Therefore, to get this to work:
You would need to change the formula to concatenate singe quotes like this:
=SUMPRODUCT(SUMIF(INDIRECT("'" & $D$5:$D$16 & "'!F:F"),A8,INDIRECT("'" & $D$5:$D$16 & "'!H:H")))
The extra bits have been emboldened.
For those seeking to broaden their data analysis capabilities beyond the SUMIF function in Excel, we highly recommend enrolling in our advanced Excel 365 training course catered specifically to businesses. This comprehensive one-day program encompasses a range of essential data analysis tools and techniques.
Now we can truly say that you have all you need to use the SUMIF Excel function across multiple sheets in Microsoft Excel.
If you feel that you or your organisation could benefit from an Excel course then please check out either our instructor led live online Excel courses or face-to-face in person Excel training courses. Let our Excel trainers give you time saving hints and tips to help you and your colleagues optimise your Excel spreadsheets.
Sign up to receive our monthly newsletter including special promotions, hints & tips, and the latest Computer Tutoring news!
