SUMIFS Formula

Cheryl Collins

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

Genevieve P.

Hi @Cheryl Collins

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

Genevieve P.

Hi @Cheryl Collins

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 Multi-Select 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

Cheryl Collins

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

Genevieve P.

Hi @Cheryl Collins

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

Cheryl Collins

Hi @Genevieve

Aah, of course 🙈 thanks, that makes perfect sense.

Thanks for your help.

Cheryl

Genevieve P.

No problem at all! I'm glad we could figure it out. 🙂