Cross sheet calculations

Luke W. · 12/22/23

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

jmyzk_cloudsmart_jp · 12/22/23

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.

Smartsheet

https://app.smartsheet.com/b/publish?EQBCT=ef87fae0f585447db7206147a599c6eb

Luke W. · 12/24/23

jmyzk_cloudsmart_jp

Dec 22, 2023

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.
https://app.smartsheet.com/b/publish?EQBCT=ef87fae0f585447db7206147a599c6eb
https://us.v-cdn.net/6031209/uploads/ZSBQ4PUUAX0O/image.png

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.

Formulas and Functions

Formulas and Functions

Cross sheet calculations

Answers

Smartsheet

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

How to return the last recorded date in a column?

What am I doing wrong?

Nested IF Statements to Determine Priority