I've spent the last hour unable to get through a relatively straight forward problem. I would like to sum a range of cells, however it cannot be a fixed formula as the rows and cells will change regularly.

I have tried combining INDEX, MATCH and SUMIF but have not been able to get anywhere. I've built a sample spreadsheet for what I'm trying to solve for, the image is attached below.

Ideally, I need to sum the values in C assuming that it matches with F2.

Help would greatly be appreciated.

Sample Spreadsheet

  • 1
    In E2 you have Employee B but in the criteria you have just the B is this the way it is in the actual data or do they match? – Scott Craner Jun 26 at 18:38
  • Sorry, they match. – Munckinn Jun 26 at 18:49
  • How about using PivotTable? – Lee Jun 27 at 2:14

SUMIFS is the way to accomplish your goal - its SUMIF with multiple criteria.

Just make sure that your sum range and criteria ranges all cover the same rows, in thisexample worksheet all the range rows are 4-12.

Please note that I changed Employee A to just A to make the criteria match the range.

If you put this formula in F3 it would do what your looking for =SUMIFS(C3:C11,A3:A11,F2,B3:B11,E3)

C3:C11 is the sum range, the other ranges are criteria ranges followed by their criteria respectively.

  • Please put the actual formula in text so the OP can copy paste, not in the photo. – Scott Craner Jun 26 at 18:54
  • Also with SUMIFS you do not need to limit the reference ranges: =SUMIFS(C:C,A:A,F$2,B:B,$E2) – Scott Craner Jun 26 at 18:56

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.