SUMIFS Formula
I am trying to create a SUMIFS formula referencing another sheet: =SUMIFS({Sheet  PTO & Project Staffing Range 2}, {Sheet  PTO & Project Staffing Range 1}, Projects@row, {Sheet  PTO & Project Staffing Range 3}, "Trainee Solicitor")
The column I am trying to add is a formula so, I have tried VALUE in front of the above formula but it isn't bringing back a value (I'm expecting 42 to come back).
If I create a SUMIF formula, excluding the last criteria for "Trainee Solicitor" it works fine so, I'm a bit stumped at what I'm doing wrong?
Thanks
Cheryl
Best Answer

Thank you for these screen captures, this helps a lot! The formula will be evaluating the criteria in each individual row, regardless of the rows above or below.
This means that the correct answer to your formula is 0, since there are no rows that say "Rushmoor" AND also say "Trainee Solicitor" in the same row. The cell in your Project column is blank wherever it says "Trainee Solicitor".
You'll want to either add a formula into these blank cells that return the Parent:
=PARENT()
Or you could have a helper column with a column formula applied, then hide this column in your sheet.
=PARENT([Project]@row)
Then adjust your first criteria range {Sheet  PTO & Project Staffing Range 1} to look at this helper column for the Parent name instead of the Project column. Does that make sense?
Cheers,
Genevieve
Answers

Can you post a screen capture of the sheet you're looking into, identifying the Column that's your "Range 3" and column type? (But please block out sensitive data). It would also be helpful to know how this formula is not working, are you receiving an error or an incorrect result?
If the result is incorrect, it may be that the formula cannot find a match in your Staffing Range 3 column to the exact words "Trainee Solicitor". Check to ensure that the text in that column is spelled the same way as what you have in your formula.
Additionally, if the column of Range 3 is MultiSelect and this is one of many options, you'll want to use the HAS function:
=SUMIFS({Sheet  PTO & Project Staffing Range 2}, {Sheet  PTO & Project Staffing Range 1}, Projects@row, {Sheet  PTO & Project Staffing Range 3}, HAS(@cell, "Trainee Solicitor"))
Let me know if any of this has helped!
Cheers,
Genevieve

Hi @Genevieve P
I am getting an incorrect result, I'm expecting to get a return of 35 against the project but am getting a 0.
I have just tried your formula including the HAS clause, as you are correct in your assumption, that range 3 is a drop down of options (Single option).
As you can see against the project Rushmoor I'm expecting a return of 35 but am getting a zero against Trainee
Thanks
Cheryl

Thank you for these screen captures, this helps a lot! The formula will be evaluating the criteria in each individual row, regardless of the rows above or below.
This means that the correct answer to your formula is 0, since there are no rows that say "Rushmoor" AND also say "Trainee Solicitor" in the same row. The cell in your Project column is blank wherever it says "Trainee Solicitor".
You'll want to either add a formula into these blank cells that return the Parent:
=PARENT()
Or you could have a helper column with a column formula applied, then hide this column in your sheet.
=PARENT([Project]@row)
Then adjust your first criteria range {Sheet  PTO & Project Staffing Range 1} to look at this helper column for the Parent name instead of the Project column. Does that make sense?
Cheers,
Genevieve


No problem at all! I'm glad we could figure it out. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!