2

I'm trying to write a formula using the SUMIFS function where criteria_range1 and criteria_range2 are columns, but criteria_range3 is a row.

For example, in the sample data below,

  1. criteria_range1 is the "Field1" column , A2:A10
  2. criteria_range2 is the "Field2" column , B2:B10
  3. criteria_range3 is the date row , C1:H1

In a separate sheet, I want to specify values for each of criteria1 → criteria3 and I want SUMIFS to add the values that meet the criteria.

In my example SUMIFS should return 163 because D2 (79) and D5 (84) meet the criteria.

How do I write the formula?

Sample Data
A B C D E F G H
1 Field1 Field2 12/03/2020 21/03/2020 17/03/2020 22/03/2020 27/03/2020 25/03/2020
2 2_testval_field1 C_testval.field2 17 79 69 79 74 35
3 5_testval_field1 E_testval.field2 78 10 57 46 91 59
4 1_testval_field1 A_testval.field2 89 41 25 92 98 53
5 2_testval_field1 C_testval.field2 91 84 62 82 37 99
6 4_testval_field1 D_testval.field2 72 65 21 97 100 71
7 6_testval_field1 C_testval.field2 84 50 68 59 14 45
8 2_testval_field1 B_testval.field2 40 17 86 30 17 69
9 3_testval_field1 C_testval.field2 74 18 62 41 14 23
10 2_testval_field1 G_testval.field2 30 12 53 25 22 26
Sample Results
A B C D E                                                                           
1 Range Criteria Function Result
2 Field1 2_testval_field1 SUMIFS 163
3 Field2 C_testval.field2
4 Date 21/03/2020
0

2 Answers 2

1

This approach first uses FILTER to return only those rows where both Field1 and Field2 match the lookup values in B2 and B3.

It then uses INDEX and MATCH to return the column that matches the lookup date in B4, and then SUM to add the values in that column.

IFERROR is added for cases where either FILTER or MATCH don't return anything.

The following formula can be placed in E2:

=SUM(INDEX(
   FILTER(data!A:H, data!A:A=B2, data!B:B=B3),,
   MATCH(B4, data!1:1, 0)))
0

Sorry, i misunderstood the task. So following formula returns sum from table only if Field1, Field2 and Date are matched

=iferror(QUERY({ARRAYFORMULA(Sheet8!A:A&Sheet8!B:B),Sheet8!C:E},
"select Col"&MATCH($B$1,Sheet8!$C$1:$E$1,0)+1&" where Col1 = '"&A2&B2&"'"),"no matches")

Test Sheet

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.