Pulling data from one sheet to another

Kyle Hicks

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

Genevieve P.

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

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.

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