Search code examples
excelexcel-formulasumifs

SUM of values if condition is true

I'm trying to do a SUM of values if the name of the task is the same.

Please find an example below.

Sheet1

       A       |                  B   
1 |   Tasks    |   Result
2 |  Task 1    |  =SUMIF(Sheet2!D2:D7;A2;Sheet2!A2:A7)
3 |  Task 2    |  =SUMIF(Sheet2!D2:D7;A3;Sheet2!A2:A7)
4 |  Task 3    |  =SUMIF(Sheet2!D2:D7;A4;Sheet2!A2:A7)

Sheet2

      A      |     B     |    C     |     D
1 | Tasks    |   City    |   Age    |   Value 
2 | Task 1   |           |          |    10 
3 | Task 1   |           |          |    12
4 | Task 2   |           |          |    20
5 | Task 2   |           |          |    8 
6 | Task 2   |           |          |    2
7 | Task 3   |           |          |    45

For task 1, expected result is 22. For task 2, expected result is 30. For task 3, expected result is 45.

However, I'm getting 0.

Can you please explain me what is wrong in the formula ?

Thanks

Solution

  • Simply use

    =SUMIF(Sheet2!A:A,A2,Sheet2!D:D)

    If you use specific range then use absolute position so that it do not chnages when you drag and down. Use-

    =SUMIF(Sheet2!$A$2:$A$7,A2,Sheet2!$D$2:$D$7)

    Good explanation here on exceljet.