Is Not Empty With Formula

[Front]

I have been working on this formula for hours. I figured out the issue, but I cannot figure out how to solve it. I want my formula to count only if two criteria's are met. However, the second criteria is looking at a cell that appears empty, but there is a formula in it and because there is a formula in it, my other formula thinks there is a value in it. How do I make it so there is no value when the cell only has a formula in it and then count only when that formula has executed.

Here is what I have:

=COUNTIFS(Table134[Reason For Opening],"*"&$AC$10&"*",Table134[Final Profit],"<>"&"")

Because of "<>"&"" counts the formula it always returns all occurrences. How do I tell it to count, but ignore the formula. I hope that makes sense.

 

[Toadstool]

Hi Front,

I'm not sure I understand without an example (e.g. I don't know what "count only when that formula has executed" means).

You could add a worker column to indicate which rows contain a formula and which not, or you could use a different approach such as SUMPRODUCT. Here it only counts if X is in the column as data entered but ignore any X as the result of a formula.

front.xlsx
	H	I
5	X	5
6	X
7	X
8	X
9	X
10	X
11	x
12	x
13	Z
14	X
Sheet1

Cell Formulas
Range	Formula
I5	I5	=SUMPRODUCT(--(ISNA(FORMULATEXT(H5:H14))=TRUE)*(H5:H14="X"))
H6,H9:H10	H6	="X"
H8	H8	=IF(1=1,"X","")

 

[Peter_SSs]

Could you use something like this?

=COUNTIFS(Table134[Reason For Opening],"*"&$AC$10&"*",Table134[Final Profit],"<1000000")

 

[bebo021999]

@Pete, second criteria: "<1000000" may not count rows with "Final profit" >=1,000,000

Try sumproduct:

=SUMPRODUCT(ISNUMBER(SEARCH($AC$10,Table134[Reason For Opening]))*(Table134[Final Profit]<>""))

 

[Peter_SSs]

@Pete, second criteria: "<1000000" may not count rows with "Final profit" >=1,000,000

That's why I said "something like" as it could easily be changed to "something like" ..

=COUNTIFS(Table134[Reason For Opening],"*"&$AC$10&"*",Table134[Final Profit],"<"&9.9E+307)

.. which should be plenty big enough and still utilise the greater efficiency of COUNTIFS.

 

[Front]

Thank you! All of those solutions worked.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.