SUMIFS with INDEX-MATCH Formula Including Multiple Criteria

Suppose you have the following dataset.

sumifs with index match multiple criteria formula

Method 1 – SUMIFS with INDEX-MATCH Combining Multiple Criteria

Steps

Select an appropriate cell (F19 in this example) and enter the following formula:

=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)

Formula Breakdown

MATCH(F16,D4:I4,0)

In this example, the formula will search for the values mentioned in cell F16 in the range of cell D4:I4, and return the column rank in that list.

INDEX(D5:I14,0,MATCH(F16,D4:I4,0))

The function will return the cell address of all the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.

SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)

The formula will sum the range value returned by the INDEX formula, only if the F17 value is present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will be ignored. After it satisfies the first criterion, it will move to the second criterion—the value of F18 in the range of cell C5:C18, only if the F18 value is present corresponding in the range of cells C5:C14.

Method 2 – Using SUMIFS with INDEX-MATCH Excluding Blank Cells

Steps

In the dataset, H9 is now blank.

Using SUMIFS with INDEX-MATCH Excluding Blank Cells

Select an appropriate cell (F19) and enter the following formula:

=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,"<>",C5:C14,"<>")

Formula Breakdown

MATCH(F16,D4:I4,0)

In this example, the formula will search for the values mentioned in cell F16 in the range of cell D4:I4, and return the column rank in that list.

INDEX(D5:I14,0,MATCH(F16,D4:I4,0))

The formula will return the cell address of all the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.

SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,”<>”,C5:C14,”<>”)

The formula will sum the range value returned by the INDEX formula. The summation will occur if there is no blank cell in the range of cells B5:B14 and C5:C14. The “<>” would set the argument in this way.

Method 3 – Combining Multiple SUMIFS with INDEX-MATCH Using OR Logic

Steps

Select a relevant cell (F18) and enter the following formula:

=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,"HP",C5:C14,F17)+SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,"Acer",C5:C14,F17)

Formula Breakdown

MATCH(F16,D4:I4,0)

The formula will search for the values mentioned in cell F16 in the range of cell D4:I4, and return the column rank in that list.

INDEX(D5:I14,0,MATCH(F16,D4:I4,0))

The formula will return the cell address of all of the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.

SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,”HP”,C5:C14,F17)+SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,”Acer”,C5:C14,F17)

The first part before the + sign indicates the summation of values from the range returned from the INDEX formula if “HP” is present in the range of cell C5:C14. After the plus sign, the structure remains the same, just the HP is replaced with Acer. Both section values are then added together.

Read More:

Method 4 – SUMIFS with INDEX-MATCH for Array Argument

Steps

Select a relevant cell (F18), and enter the following formula:

=SUM(SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,{"HP","Acer","Dell"},C5:C14,F17))

Formula Breakdown

MATCH(F16,D4:I4,0)

This formula will search for the values mentioned in cell F16 in the range of cell D4:I4, and return the column rank in that list.

INDEX(D5:I14,0,MATCH(F16,D4:I4,0))

This formula will return the cell address of all of the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.

SUM(SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,{“HP”,”Acer”,”Dell”},C5:C14,F17))

The formula will sum values in the range only if the “HP”, “Acer”, and “Dell” values are present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will be ignored. After it satisfies the first criterion, it will move to the second criterion, and so on. It will be for the value of F18 in the range of cell C5:C18. They would sum value in the range of H5:H14 only if the F18 value is present corresponding in the range of cells C5:C14.

Method 5 – SUMIFS with INDEX-MATCH for Dates

Steps

Then select the correct cell (F19) and enter the following formula:

=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,">="&F18)

Formula Breakdown

MATCH(F16,D4:I4,0)

The formula will search for the values mentioned in cell F16 in the range of cell D4:I4, and return the column rank in that list.

INDEX(D5:I14,0,MATCH(F16,D4:I4,0))

The formula will return the cell address of all the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.

SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)

The formula will sum values in the range only if the F17 value is present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will be ignored. After it satisfies the first criterion, it will move to the second criterion. The criteria here are data-related. If the values are after the date mentioned in cell F18, then they are going to be summed.

Method 6 – SUMIFS with INDEX-MATCH Using Comparison Operator

Steps

Then select the appropriate cell (F19) and enter the following formula:

=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),">=10000")

Formula Breakdown

MATCH(F16,D4:I4,0)

This formula will search for the values mentioned in cell F16 in the range of cell D4:I4. And return the column rank in that list.

INDEX(D5:I14,0,MATCH(F16,D4:I4,0))

This formula will return the cell address of all of the rows in the column returned in the MATCH formula, in the mentioned range cells D5:I14. The output, in this case, is H5:H14.

SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),”>=10000″)

The formula will sum value in the range only if the F17 value is present in the range of cells B5:B14. If the value is not present in any row, the corresponding row value in the range of cell H5:H14 will not count. After it satisfies the first criterion, it will move to the second criterion. For the values, only those that are over 10,000 will be added. The last part of the formula ensures this.