# Google Sheets - SUMIFS with both Rows and Columns

I'm trying to write a "SUMIFS" formula that uses both rows and columns.

Let's say I have "Field 1" in Column A from A2:A10, "Field 2" in Column B from B2:B10, and Dates across the top of my sheet from cells C1:E1. And then there are values in cells C2:E10.

On another tab, I allow the user to select from Field 1 options in A2, and then Field 2 options in B2. In B1, they can select a Date. So in cell C2, I want it to sum all values in the first sheet where Field 1, Field 2 and Date match the users inputs.

• Welcome. Please share a copy of your spreadsheet. Mar 10, 2020 at 4:32

## 1 Answer

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

• Thanks for the help - but unfortunately this looks like it doesn't quite do the trick for my use case. If I'm understanding the logic right, this formula adds the total of matches to the first column and the total of the matches to the second column. But what I need is for it to only sum the totals if BOTH match Mar 11, 2020 at 14:44
• I should also mention, doesn't the index/match logic only find the first match, rather than ALL matches? I need it to act more like a sumifs, not a vlookup. Thanks again though for the help! Mar 11, 2020 at 14:58
• oh, ok i misunderstood the task. Mar 11, 2020 at 15:03