Cross sheet calculations
Hello all and happy holidays! I'm currently trying to find a solution for the following:
I have an excel spreadsheet that has data manually entered into it on a daily basis. At the end of a week, this what it looks like.
I have been converting this process over to Smartsheet and using a form to capture the daily data entries.
I'm trying to put together a calculation sheet that essentially looks like spreadsheet above, so that I can create some nice dashboards for data analysis.
I would use simple countifs formula, but the teams do not stay on static shifts. They change from week to week.
Here is a shot of the sheet that is collecting the data from the form entry:
Need help finding the best way to calculate the entries so that they are similar in format to the excel sheet above.
VLOOKUP? INDEX? MATCH? COUNTIFS? My head hurts....haha!
Kind Regards,
Luke
Answers
-
Hi @Luke W.
I would use a COUNTIF formula something like this;
=COUNTIFS({Dirty Wheat}, NOT(ISBLANK(@cell)), {Crew}, Team@row, {Date}, Date@row, {Shift}, Shift@row)
The range {Dirty Wheat} refers to the Dirty Wheart column in the Crew Data sheet in the second image, and so do the {Crew},{Date}, and {Shift} ranges.
I did not understand the meaning of color balls in the Dirty Wheat column, etc., so I counted the number of cells that were not blank.
-
I'll give this a go. Thanks for your resonse!
The green balls are indicative of a "good" status. There are three choices, "green", "yellow", "red".
For this solution, I'm only counting the "good" response. I'll report back after I've had a chance to try it.
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!