Counting formulas referring to a range containing formulas

[sevas1994]

Dear Excel community,

I'm learning counting functions and noticed that they give me wrong answers when referring to a range containing formulas.

Please refer to range K3:K9 and feel free to comment

https://docs.google.com/spreadsheet...VpN6C6698ZKdv_ybBuxrkKNz8/edit#gid=1299953844
https://docs.google.com/spreadsheets/d/1mbVFXspU9ml1_epBhIVpN6C6698ZKdv_ybBuxrkKNz8/edit?usp=sharing

Many thanks in advance

 

[sevas1994]

Thanks everyone for the answers

 

[sevas1994]

This formula counts non-blank values perfectly. But why does the formula countif(A2:D13; "*") gives 36? What does it include and exclude? Another weird thing. I wrote 0 in E2 and F2 is blank containing no formula. When I compare E2 and F2 with =E2=F2 it gives true, while exact formula gives false. However when D3 which contains formula and is "" is compared with E2 (=D2=E2) it gives false but when it's compared with F2 (=D2=F2) it gives true. =exact also gives true when D2 is compared with F2.

 

[Aladin Akyurek]

This formula counts non-blank values perfectly. But why does the formula countif(A2:D13; "*") gives 36? What does it include and exclude? Another weird thing. I wrote 0 in E2 and F2 is blank containing no formula. When I compare E2 and F2 with =E2=F2 it gives true, while exact formula gives false. However when D3 which contains formula and is "" is compared with E2 (=D2=E2) it gives false but when it's compared with F2 (=D2=F2) it gives true. =exact also gives true when D2 is compared with F2.

1.

? and * are the so-called wild cards.

? stands for 1 token whose length (LEN) is 1; * stands for null or more tokens.

"?*" as criterion in COUNTIF(S) mean any text of length 1 or >1.

Thus:

=COUNTIFS(A2:D13,"?*")

counts all all text entries whose length >=1.

=COUNTIFS(A2:D13,"*")

counts all text entries including formula blanks (i.e., "").

2.

=E2=D2

is TRUE also (a) when E2 = 0 and F2 empty (does not contain any formula) or vice versa, because a formula in Excel reads an empty cell as 0; (b) when E2 contains a formula with "" as result (say ="") and F2 is empty.

Note that:

=EXACT(E2,F2)

under (a) >> FALSE and under (b) >> TRUE. EXACT assumes number comparison under (a), prompted by the presence of a 0 and text comparison under (b) prompted by the presence of "".

Hope this helps.

 

[sheetspread]

Similar to Aladin's excellent answer:

Excel 2010
	A	B	C	D	E	F	G	H
1	True Blank	""
2			TRUE	FALSE			ISBLANK(A2)	ISBLANK(B2)
3			TRUE				EXACT(A2,B2)
4			TRUE				A2=B2
5			0	0			LEN(A2)	LEN(B2)
6			0	1			COUNTIF(A2,"*")	COUNTIF(B2,"*")
7			0	0			COUNTIF(A2,"?*")	COUNTIF(B2,"?*")
Sheet17

Cell Formulas
Range	Formula
B2	=""
C2	=ISBLANK(A2)
C3	=EXACT(A2,B2)
C4	=A2=B2
C5	=LEN(A2)
C6	=COUNTIF(A2,"*")
C7	=COUNTIF(A2,"?*")
D2	=ISBLANK(B2)
D5	=LEN(B2)
D6	=COUNTIF(B2,"*")
D7	=COUNTIF(B2,"?*")

 

[sevas1994]

Many thanks for the answer.

Similar to Aladin's excellent answer:

Excel 2010

	A	B	C	D	E	F	G	H
1	True Blank	""
2			TRUE	FALSE			ISBLANK(A2)	ISBLANK(B2)
3			TRUE				EXACT(A2,B2)
4			TRUE				A2=B2
5			0	0			LEN(A2)	LEN(B2)
6			0	1			COUNTIF(A2,"*")	COUNTIF(B2,"*")
7			0	0			COUNTIF(A2,"?*")	COUNTIF(B2,"?*")

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell Formula B2 ="" C2 =ISBLANK(A2) D2 =ISBLANK(B2) C3 =EXACT(A2,B2) C4 =A2=B2 C5 =LEN(A2) C6 =COUNTIF(A2,"*") C7 =COUNTIF(A2,"?*") D5 =LEN(B2) D6 =COUNTIF(B2,"*") D7 =COUNTIF(B2,"?*") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[sevas1994]

Many thanks for the answer.

1.

? and * are the so-called wild cards.

? stands for 1 token whose length (LEN) is 1; * stands for null or more tokens.

"?*" as criterion in COUNTIF(S) mean any text of length 1 or >1.

Thus:

=COUNTIFS(A2:D13,"?*")

counts all all text entries whose length >=1.

=COUNTIFS(A2:D13,"*")

counts all text entries including formula blanks (i.e., "").

2.

=E2=D2

is TRUE also (a) when E2 = 0 and F2 empty (does not contain any formula) or vice versa, because a formula in Excel reads an empty cell as 0; (b) when E2 contains a formula with "" as result (say ="") and F2 is empty.

Note that:

=EXACT(E2,F2)

under (a) >> FALSE and under (b) >> TRUE. EXACT assumes number comparison under (a), prompted by the presence of a 0 and text comparison under (b) prompted by the presence of "".

Hope this helps.

 

[Aladin Akyurek]

Many thanks for the answer.

You are welcome.