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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!