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.

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

1 Answer 1


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

"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
    – Alex Hahn
    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!
    – Alex Hahn
    Mar 11, 2020 at 14:58
  • oh, ok i misunderstood the task.
    – Oleg_S
    Mar 11, 2020 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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