How to Use VLOOKUP and SUMIFS
I have 2 sheets. The first one is for case reviewer #1. It lists the case ID and the hours each day they worked on it. In total I have 3 reviewers and each have their own time sheet. Then, I have a summary sheet that lists the Case ID, each reviewer, and then the total time spent on each case. What I would like to do is on my summary sheet for each case and each reviewer, I would like to look up their sheet and sum the total hours they spent on each case so I do not have to manually enter the hours spent for each case for each reviewer. For example, screenshot 2 shows reviewer 1 spent 8 total hours on case 123, 16 on 456 and 7 on 789. I attached screen shots below. Thanks in advance!
Best Answer
-
Once you type out the function there should be a small dialog box that appears. In that dialog box is a link to reference another sheet. Once you click on that link you will select the sheet to reference and then the range to reference on that sheet and then click on the insert reference button.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You would need to use something along the lines of...
=SUMIFS({Reviewer 1 Sheet Hours Column}, {Reviewer 1 Sheet Case ID Column}, [Case ID]@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Here is what I tried:
=SUMIFS({DP Time Study 6}, {DP Time Study 1}, [2018039]@row)
"DP Time Study" is the name of Reviewer 1's Sheet. Column 1 is the case ID#s and Column 6 is the Hours. "2018039" is the case I am looking to count all the hours for.
This code came out as unparseable
-
You need to use the column name that actually houses the case number in that 3rd portion.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Tried it both this way: =SUMIFS({DP Time Study 6}, {DP Time Study 1}, [Case Number or Cannot Be Allocated (CBA)], "2018039")
and =SUMIFS({DP Time Study 6}, {DP Time Study 1}, [Case Number or Cannot Be Allocated (CBA)]:[Case Number or Cannot Be Allocated (CBA)], "2018039")
I attached 2 more screen shots. The first one is the sheet I want to extract the data from (DP Time Study), the second is where I want it to go (Hour Summary). So when the "Case Number of Cannot Be Allocated (CBA) is "2018039", add up all of the hours in the Duration Hours that go with that case number. (I know in my screenshot its a different case number). Was I correct that "Case Number or Cannot Be Allocated (CBA)" is {DP Time Study 1} and Duration Hours is {DP Time Study 6}?
-
=SUMIFS({DP Time Study 6}, {DP Time Study 1}, [Case ID]@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It is giving back #INVALID REF
-
You are going to need to make sure you are creating your cross sheet references properly.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul, that is what I do not know how to do
-
Once you type out the function there should be a small dialog box that appears. In that dialog box is a link to reference another sheet. Once you click on that link you will select the sheet to reference and then the range to reference on that sheet and then click on the insert reference button.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ok, thank you for your help! It worked!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!