Pulling multiple information from one sheet to another

Options

Hi everyone!

I need to calculate some information based on several criteria.

I have a sheet called Quality and in it, there is a lot of information. but the columns i need is; Date, Product type, Good quality and total quantity.

  • In the product type collums we have 3 different products, but we only need 2 for this calculation. (Premium and premium plus)
  • in the date columns there are many dates from this past couble of months

So, we want to see the percentage of how many of the products (premium & Premium plus) where of good quality (good quality / Total quantity) in the past 5 days (working days).

We also want the days to change with the next day, so it automatically updates.

Hope somebody can help us with this, we are preparing it to be in another sheet so the calculation will be in a different sheet, that we can alter as we want.

Best R.

Marie

Answers

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    Use the COLLECT function in a Count function

    This will count only the good Premium and Premium plus product in the last 5 days

    =Count(Collect({Quantity},{Type},"Premium",{Type},"Premium plus",{Date},>today(-5),{Quality},"Good"))

    This will count ALL Premium and Premium plus product in the last 5 days

    =Count(Collect({Quantity},{Type},"Premium",{Type},"Premium plus",{Date},>today(-5)))

    Devide one by the other, either in one cell, or show the total of each fonction and devide them.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!