# How do I use SUMIFS when the criteria ranges include both rows and columns?

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`

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)))
``````

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