Search code examples
excelsumifs

excel SUMIFS with 3 criteria in two rows

I'm having trouble making SUMIFS to work for my sheets.

I have Sheet1 and Sheet2.

Sheet1 is a payment plan detail for accounts:

  1. Row A is the ID of accounts.
  2. Row D is accounts' payment terms.
  3. Row H is what I want to sum depending on the following conditions:

  4. 1, Match the account ID in Sheet2

    2, Only sum terms from P to Q in Sheet2.(>=P, <=Q)

I'm hoping to add this code at the end of each row in Sheet2

For example,

For Row 2 in Sheet2: account no. 180723540400645 needs to sum term 4-24.

We look at Sheet1, and for all rows in column A that equals "180723540400645", sum column H when column D is between 4 and 24.

My code is =SUMIFS(Sheet1!H:H,Sheet1!A:A,"=Sheet2!C2",Sheet1!D:D,">=P2",Sheet1!D:D,"<=Q2")But it keeps getting 0. Sheet1

Sheet2

Thanks!

EDITS: I tried to just get the sum of all matching account ID in Sheet1, according to a comment. But it's still zero somehow... =SUMIFS(Sheet1!H:H,Sheet1!A:A,"=Sheet2!C3")

Solution

  • Your formula should read:

    =SUMIFS(Sheet1!H:H,Sheet1!A:A,Sheet2!C2,Sheet1!D:D,">="&P2,Sheet1!D:D,"<="&Q2)