SUMIFS with Contact Cells

J Powers

I have employees submitting their time sheets to an intake form. I have a summary sheet that calculates their total hours per time code. I am finding that some employees are submitting their emails, and some are submitting their contact card. This is an issue when trying to add up hours since it will only calculate either the email address, or the name. I would imagine there are several approach to this but I am struggling with what the easiest solution would be.

Summary Sheet Formula:

Summary Sheet:

Time sheet Intake Sheet (Email was Submitted)

Time sheet Intake Sheet (Contact Card / Name was Submitted)

Can I force employees to submit their form with the contact card / Name? Or can I add an OR function to my formal so it sums if it is an employee email OR their name.

Thanks

Paul Newcome

=SUMIFS({Timesheet Intake Sheet Range 2}, {Timesheet Intake Sheet Range 1}, OR(@cell = $[Tech Email]2, @cell = "John Doe"), {Timesheet Intake Sheet Range 3}, >=$[Pay Period Start]$1, {Timesheet Intake Sheet Range 3}, <=$[Pay Period End]$1, {Timesheet Intake Sheet Range 4}, "Approved", {Timesheet Intake Sheet Range 5}, "Yes", {Timesheet Intake Sheet Range 6}, "No")

You can replace "John Doe" with either specific text or a cell reference if you have the name stored in another column.

Paul Newcome

The OR function would probably be the easiest way to go. What is your current formula?

J Powers

=SUMIFS({Timesheet Intake Sheet Range 2}, {Timesheet Intake Sheet Range 1}, $[Tech Email]2, {Timesheet Intake Sheet Range 3}, >=$[Pay Period Start]$1, {Timesheet Intake Sheet Range 3}, <=$[Pay Period End]$1, {Timesheet Intake Sheet Range 4}, "Approved", {Timesheet Intake Sheet Range 5}, "Yes", {Timesheet Intake Sheet Range 6}, "No")

Paul Newcome

=SUMIFS({Timesheet Intake Sheet Range 2}, {Timesheet Intake Sheet Range 1}, OR(@cell = $[Tech Email]2, @cell = "John Doe"), {Timesheet Intake Sheet Range 3}, >=$[Pay Period Start]$1, {Timesheet Intake Sheet Range 3}, <=$[Pay Period End]$1, {Timesheet Intake Sheet Range 4}, "Approved", {Timesheet Intake Sheet Range 5}, "Yes", {Timesheet Intake Sheet Range 6}, "No")

You can replace "John Doe" with either specific text or a cell reference if you have the name stored in another column.

J Powers

That worked. Thank you very much!

Paul Newcome

Happy to help! 👍️