Can a formula not include references to other cells whose values are based on formulas?

Rashaun G

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.

Genevieve P.

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?

Julie Fortney

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

Rashaun G

@Julie Fortney Thanks for reaching out. I am having no luck with that either, still getting no match error.

Genevieve P.

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

Rashaun G

@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))

Genevieve P.

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?

Rashaun G

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