Cross sheet calculations

Options
✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!