SUMIFS where one of the conditions is at the top of table

Sample: Two tables in one excel sheet.

Criteria 1:

ColumnA | ColumnB
Name    | Amount
John    | 3
Smith   | 2
John    | 6

Criteria 2:

Name    | Amount
John    | 10
Smith   | 20

I would like to perform => SUMIFS range is B:B and where the table name is "Criteria 1".

Note: there are a lot more names and criteria #.

CodePudding user response：

Have you tried in this way? So what i have done is, i have created a Defined Named Ranges for Criteria 1 and use the same within an INDEX Function so that gives a range which can be used within SUMIFS Function - SUM_RANGE & CRITERIA RANGE

So the formula used in Cell B7

=SUMIFS(INDEX(Criteria_1,,2),INDEX(Criteria_1,,1),$A7)

Where Criteria_1 is Defined Named Ranges, how to create it, follow the steps

• Select the whole range i.e. in the image below, A2:B4

• Press ALT F3 and in the NAME BOX write Criteria_1

This is one way to define a range -->

The other way is select the range from Formulas Tab, Under Defined Names Group select Define Name. A new pop up shows --> Name it as Criteria_1 and refers to already has been selected as the range required, and press OK

Therefore as per the sample provided in my sheet the

Where Criteria_1 is ='Blad (24)'!$A$2:$B$4

Blad is sheet name so don't get confused!

CodePudding user response：

Formula in E2

 =SUMIFS($B$2:$B$100;$A$2:$A$100;D2)

Maybe good to convert your data to a standard table, so the formula will update when you add more data to it.