Pulling data from one sheet to another

I am currently in the process of creating a dashboard. I was originally planning to use cellular formulas to pull this data. The original formula I was using is:

=COUNTIFS({JBO Type}, "Overhead", {Date}, IFERROR(MONTH(@cell), "") = 11)

However, this formula is returning an improper total, it reads "24" when it should be "42." I know this because I double/triple check my numbers before pushing them out to management.

I was able to get the data I need using a sheet summary with the formula:

=COUNTIFS([JBO Type]:[JBO Type], "Overhead", Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 31)))

This is great, however it extremely limits the way I can display the data in a dashboard due to it being in a sheet summary.

How can I write this so I can embed it within a sheet and not limit my displays on a dashboard?

Thanks in advance,

Kyle

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kyle Hicks

    You should be able to write the exact same formula as a cross-sheet formula, like so:

    =COUNTIFS({JBO Type}, "Overhead", {Date}, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 31)))

    That said, it should return the same number as your previous formula, as long as you have no other Years present in the Date column:

    =COUNTIFS({JBO Type}, "Overhead", {Date}, IFERROR(MONTH(@cell), "") = 11)

    If you're seeing an incorrect number, is it possible that your formula is only finding some of the matches due to the JBO Type column type? For example, if this is a multi-select column, using "Overhead" as the criteria will mean it will only count cells where this is a single value selected.

    If it's multi-select, try using HAS:

    =COUNTIFS({JBO Type}, HAS(@cell, "Overhead"), {Date}, IFERROR(MONTH(@cell), "") = 11)


    You can also translate Sheet Summary Fields into a Sheet Summary Report (so you can create charts out of the data) if that helps.

    If none of this has worked, it would be useful to see a screen capture of your source sheet, identifying how your two columns are populated (manually or through a formula?), but please block out sensitive data.

    Cheers,

    Genevieve

  • Kyle Hicks
    Kyle Hicks ✭✭✭

    Genevieve,


    I had tried multiple avenues to get the numbers to populate properly. Cellular formulas were not doing the trick for me, so I resorted to Sheet Summaries. These numbers reflect properly. Thanks for your help

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kyle Hicks

    I'm glad you were able to find a solution that worked for you. If the end-goal is to be on a Dashboard, then I would recommend creating a Sheet Summary Report to leverage the data you've collected.

    Cheers!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!