SUMIFS with Contact Cells
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
Best Answer
-
=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.
Answers
-
The OR function would probably be the easiest way to go. What is your current formula?
-
=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")
-
=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.
-
That worked. Thank you very much!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!