SUMIF Not Working

Chloe Rose

Hi,

I'm running into issues when trying to use as SUMIF function in my sheet summary. I need to have it add together all the percentages in the red square under the column "Capacity Rating." The goal is to be able to create a SUMIF for each project manager in the division so we can see what their workload capacity is before assigning projects. I've used the SUMIF to pull from the "Key PM Email" column and then the "Capacity Rating" column. The percentage in the capacity rating column is being generated by the following NestedIF function:

=IF(AND(PME@row = "easy", PCR@row = "intermediate"), "50%", (IF(AND(PME@row = "easy", PCR@row = "easy"), "25%", IF(AND(PME@row = "easy", PCR@row = "advanced"), "100%", IF(AND(PME@row = "intermediate", PCR@row = "easy"), "10%", (IF(AND(PME@row = "intermediate", PCR@row = "intermediate"), "20%", IF(AND(PME@row = "intermediate", PCR@row = "advanced"), "50%", IF(AND(PME@row = "advanced", PCR@row = "easy"), "10%", (IF(AND(PME@row = "advanced", PCR@row = "intermediate"), "15%", IF(AND(PME@row = "advanced", PCR@row = "advanced"), "25%", IF(AND(PME@row = "experts only", PCR@row = "easy"), "10%", (IF(AND(PME@row = "experts only", PCR@row = "intermediate"), "15%", IF(AND(PME@row = "experts only", PCR@row = "advanced"), "25%", IF(AND(PME@row = "experts only", PCR@row = "experts only"), "25%", "0"))))))))))))))))

This seems like it should be straight forward but everytime I attempt to create the SUMIF I get #BLOCKED, 0, or #UNPARSEABLE. I speculate that the issue is coming from the fact that the Capacity rating percentages it's trying to read is coming from the NestedIF. But I have no idea. Is there a workaround for this? Or is there a way automate it so that the Capacity rating % for each row gets automatically copied to a different cell in a different column and pull from that # for the SUMIF so that the % isn't coming from a cell with a function already in it?

Genevieve P.

Hi @Chloe Rose - 8PCC

I answered your other post (see here), but the main reason this is unable to SUM is the way that the initial formula is returning the Percent numbers... it's producing them as text versus as numerical data that can be summed. Please see my answer in your other post and let me know if you have any questions!

Cheers,

Genevieve