Excel tutorials

How to Sum Up all Values for Two Conditions from Multiple Worksheets

In this Excel tutorial I will show how we can from multiple worksheets add all values if two conditions match. Notice the look of the workbook on the picture below. Note the worksheet 'Master' on which the sum of all the values from the other worksheets: 'jan', 'feb' and 'mar'. Also note the table "Table1" in the "I" column.

The 'Master' worksheet in 'A' and 'B' column contains two conditions. The first condition is the date and the second condition is the person's name. Displayed are two ways of adding (Sum1 and Sum2).

Sum of values from multiple worksheets if date and text match

Worksheet 'jan'

On this worksheet are the source data for the month of 'January'. Here are the payouts for each individual and a specific date.

Summarize data from the first worksheet

Worksheet 'feb'

This worksheet contains the source data for 'February'.

Source data for February

Worksheet 'mar'

This worksheet contains the original data for the month 'March'.

Source data for March

An example of a formula for the sum of multiple values from multiple worksheets if they met two criteria

Example of Excel formula in 'C2' cell:

The 'C' column contains the SUMIFS formula. In the 'C2' cell is the sum of all values from multiple worksheets for two conditions. In this case this is a SUM for two criteria the Date and Name of the person. The basic function of this formula below is SUMIFS. As you know, SUMIFS uses multiple criteria and multiple ranges but only if they are on a single worksheet. This formula does not work with the so-called 3D range or more ranges that are on multiple worksheets. But we can duplicate this formula and use multiple formulas as one.

Sintax:
=SUMIFS(sum_range;criteria_range;criteria;...)

Formula

=SUMIFS(jan!$C$2:$C$10;jan!$A$2:$A$10;A2;jan!$B$2:$B$10;B2)+SUMIFS(feb!$C$2:$C$10;feb!$A$2:$A$10;A2;feb!$B$2:$B$10;B2)+SUMIFS(mar!$C$2:$C$10;mar!$A$2:$A$10;A2;mar!$B$2:$B$10;B2)

Example of Excel formula in 'D2' cell:

In the column 'D' there is a formula that is based on the SUMPRODUCT function. If you look at the arguments of this function, then you can see that all the arguments are the same. In the theory SUMPRODUCTt function multiplies and sums up.

=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&Table1&"'!$C$2:$C$10");INDIRECT("'"&Table1&"'!$A$2:$A$10");A2;INDIRECT("'"&Table1&"'!$B$2:$B$10");B2));)

  • The syntax of this Sumproduct function follows

SUMPRODUCT(array;array,array;...). So if you look at the final formula, you can see that only one Sumproduct function argument is used. =SUMPRODUCT(SUMIFS).

  • The SUMIFS syntax functions in this case are as follows:

SUMIFS(sum_range;criteria_range1;criteria1;[criteria_range2;criteria2];...)

SUMIFS(INDIRECT("'"&Table1&"'!$C$2:$C$10");INDIRECT("'"&Table1&"'!$A$2:$A$10");A2;INDIRECT("'"&Table1&"'!$B$2:$B$10");B2)

Of course, if you put these formulas as stand-alone, the result would be a "VALUE!" Error. But when we neste SUMIFS formula within SUMPRODUCT formula then we have the correct result. Note that this formula has nested the INDIRECT function (This is a volatile function). It helps us to use multiple worksheets, because from the auxiliary 'Table1' drag the worksheet names.

I hope I have successfully explained how Excel works for the SUM of all values from multiple worksheets if two conditions match.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.