Multiple SUMIF within a single cell?

Jaraldo

Hello, I'm trying to build a template which would allow vendors to log hours against a project and automatically calculate a cost for each task. This calculation looks at the name of the individual reporting hours along with the listing of approved hours for the task then multiplies it against that individual's hourly rate which is stored within the same sheet.

Column Headers for this formula are: Name, Hours Approved, Contact Name, Hourly Rate

One side of this sheet contains a contact list with the Contact Name and Hourly Rate. The other side of the sheet keeps a running list of all logged tasks. Based on who is reporting the task (Column "Name") the formula needs to match it to the matching name (Column "Contact Name") and multiply the reported hours (Column "Hours Approved") by the hourly rate for that particular individual (Column "Hourly Rate").

Right now the formula is built to assume that there are four vendors doing work on this project. I get the following error with it though: #UNPARSEABLE.

=SUMIF(Name1, =[Contact Name]1, [Hours Approved]1 * [Hourly Rate]1), SUMIF(Name1, =[Contact Name]2, [Hours Approved]1 * [Hourly Rate]2), SUMIF(Name1, =[Contact Name]3, [Hours Approved]1 * [Hourly Rate]3), SUMIF(Name1, =[Contact Name]4, [Hours Approved]1 * [Hourly Rate]4)

 

Any help would be appreciated. 

Thanks!

Shaine Greenwood

Hi,

Since you're using single cells (instead of a range), I think you'd be better served by using an IF statement or possibly the LOOKUP function:

IF—https://help.smartsheet.com/function/if

LOOKUP—https://help.smartsheet.com/function/lookup

An example of an IF statement would be:

=IF(Name1 = [Contact Name]1, [Hours Approved]1 * [Hourly Rate]1, IF(Name1 = [Contact Name]2, [Hours Approved]1 * [Hourly Rate]2, IF(Name1 = [Contact Name]3, [Hours Approved]1 * [Hourly Rate]3, IF(Name1 = [Contact Name]4, [Hours Approved]1 * [Hourly Rate]4))))

If you want to use the LOOKUP function, it might be easier in the long run, but it would require that you organize your information into a lookup table first. (Your sheet might already be organized in that structure. See the above linked help article for more info.)