Calculation from Sheet with shuttled Data to combined data

Jenifer J.

I have a sheet that is refreshed every morning to add and update existing data from an in-house data warehouse. I am trying to create a sheet that pulls from the original sheet and can produce a graph in dashboard that tells me how long something has been in a current state, with many things being in that state for various time periods. It would be great if I could color code them for different time ranges. Similar to this chart. Is that possible? I already have the formula to count days as a column formula in the original sheet.

Gia Thinh

Try this modified formula by adding OR() and @cell:

=COUNTIFS({SP Contact}, OR(@cell=[Primary Column]@row, @cell=additional contact), {Current Queue}, "SAM: Awaiting Sample QC
")

Gia Thinh

Hi @Jenifer J.

Yes. it is posible for you to create a Stacked Column chart on your dashboard as the screenshot below. You may need to add more columns to your sheet for calculating different time ranges.

Jenifer J.

Thank you so much @Gia Thinh

Follow up question, because my datasheet source is a daily shuttle that updates every morning. Do you have a calculation you used to gather those numbers for each column from a larger data set? I am guessing it will be a very large if than statement. I would really apricate any additional help!

Gia Thinh

Basically, I often use COUNTIFS or SUMIFS to count or summarize a range/column with multi conditions. Look at the screenshot below for your reference.

Jenifer J.

Hi @Gia Thinh

Thank you for your example, I can get a Count if formula to work to get the data into a table similar to your example, but as soon as I try to put an "or" to add an addition SP contact to look for, it gives me an error. Can you advise on how to do it with my formula below?

=COUNTIFS({SP Contact}, [Primary Column]@row, {Current Queue}, "SAM: Awaiting Sample QC
")

Gia Thinh

Try this modified formula by adding OR() and @cell:

=COUNTIFS({SP Contact}, OR(@cell=[Primary Column]@row, @cell=additional contact), {Current Queue}, "SAM: Awaiting Sample QC
")

Jenifer J.

Thank you so much @Gia Thinh !