Multiple IFs then SUM
I have developed individual project sheets. In the project sheet I have multiple tasks. I want to populate number of hours I worked on each task, by summing the number of hours I worked on that project prior to a certain date.
I keep all my hours worked on various projects in a work log which I am referencing from the individual project sheets. In my work log, I put the date in the [Date] column, the name of the project I worked on in the [Note] column, and the hours I worked on the project that day in the [Hours] column.
So, in the worklog, for the rows that are between a certain date range in the [Date] column AND for the rows that also have the "specific project name" in the [Note] column, then SUM the corresponding hours from the [Hours] column and populate the cell in the individual project sheet.
For example:
IF [REF:Date] is between 1/1/21 to 1/29/21 AND IF [REF:Note] = "Name Project Work", then SUM [REF:Hours]
I have tried multiple ways to do this using SUMIF and IF/AND statements but I keep getting an "unparseable" return error.
Does anyone have any ideas on how I can do this? Thank you!
Answers
-
@Aiman Shahpurwala Where do you want the results to show? Are they supposed to be on the individual project sheets?
I think the overall environment is simplified by using another sheet specifically for metrics. In this metric sheet you can build the formulae you need for the calculations. Then from the project sheet you link to that result.
If that makes sense, let me/us know if you need help with the layout.
Dale
-
Hi Dale,
Yes, I would like the results to show up on the individual project sheets. The summed hours would be associated with task under the project sheet. Does that make sense?
It might be helpful to see what this looks like, if you are able to provide some support with the layout.
Thank you!
-
@Aiman Shahpurwala I am building a similar environment for internal use, so I think I know what you are after. Give me a few days to show you an outline.
Dale
-
Great thank you Dale!
-
@DMurphy just following up on our conversation.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!