Can a formula not include references to other cells whose values are based on formulas?
A formula is returning "No Match" in the example pictured below. The formula seems correct based on another sheet it is working in. My only guess is that a formula cannot reference two other cells that each have values based on formulas themselves.
Any thoughts are appreciated.
Best Answer
-
Hi @Rashaun G
My apologies, I should have clarified. You'll need to add the IFERROR around the formulas themselves... whatever you have in the "Item Status" column and the "Batch" column, so that those cells present something blank or a different value.
Can you clarify what those two other formulas are? I'd be happy to show you. What's in your "A04. Batch" column?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Rashaun G, in the example row you highlighted, do you want to count the number of rows in which the A01a. Item Status is "09. Client First Review", and the A04. Batch column equals 1?
If so, remove the $ in front of [DB1]@row, and change $[DB2]1 to $[DB2]$1.
www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt
-
@Julie Fortney Thanks for reaching out. I am having no luck with that either, still getting no match error.
-
Hi @Rashaun G
Two things to check here! 🙂
First, is there a "No Match" error anywhere else in your sheet? Formula errors will bubble up, so if one cell has an error in your "Batch" column, then this will show in your current formula.
Try wrapping an IFERROR around your Batch formula, like so:
=IFERROR(formula, "")
Then if this hasn't helped, I notice that the value in the top of your sheet (the "1") appears on the left side of the cell, indicating that it's seen as text instead of as numerical. Try wrapping your cell reference in a VALUE function to make sure the 1 is seen as 1:
VALUE($[DB2]$1)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Thanks for those tips. I did noticed "no match" errors in both the status and batch columns I am referencing. So I added an iferror function to both the status and batch columns references in the formula as follows and pictured. I tried that with and without the Value function added and while I am no longer getting a no match error, I am only getting 0 where, as pictured, item #13, for example, has more than 0 instances among other items. I think we are getting closer.
=COUNTIFS(IFERROR([A01a. Item Status]:[A01a. Item Status], " "), [DB1]@row, IFERROR([A04. Batch]:[A04. Batch], " "), VALUE($[DB2]$1))
-
Hi @Rashaun G
My apologies, I should have clarified. You'll need to add the IFERROR around the formulas themselves... whatever you have in the "Item Status" column and the "Batch" column, so that those cells present something blank or a different value.
Can you clarify what those two other formulas are? I'd be happy to show you. What's in your "A04. Batch" column?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Good morning @Genevieve P.
Your solution and clarification helped. Once I incorporated the iferror function into the correct columnar formulas the other calculations worked flawlessly.
Thanks again to you and @Julie Fortney for the help and helping me learn more things regarding functions & formulas.
Kind regards,
Rashaun
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!