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

 

[Aladin Akyurek]

Care to post the COUNTIF formula giving you the wrong results?

 

[sheetspread]

Countblank will count "" as blank, CountA will not. That's all I can think of since:

Excel 2010
	A	B	C	D	E	F
1
2	the	the1	book	1
3			my
4			mine
5			your
6	a	a1	library	5
7	a	a2	pen	6
8	an	an1	apple	7
9			Germany
10			England
11	the	the2	librarian	10
12	the	the3	forest	11
13	an	an2	uncle	12		33
14						15
Sheet16

Cell Formulas
Range	Formula
F13	=COUNTA(A2:D13)
F14	=COUNTBLANK(A2:D13)

 

[sevas1994]

Sure, please. Many thanks for your time, in advance.

				Formulas in B column	Formulas in D column		a	an	the
the	the1	book	1	=A2&IF(A2="", "", COUNTIF($A$2:A2, A2))	=IF(B2="", "", COUNTA($B$2:B2))	1	library	apple	book	Formulas in L column
		my		=A3&IF(A3="", "", COUNTIF($A$2:A3, A3))		2	pen	uncle	librarian	=counta(B2:B13)	12
		mine		=A4&IF(A4="", "", COUNTIF($A$2:A4, A4))		3	#N/A	#N/A	forest	=count(D2:D13)	7
		your		=A5&IF(A5="", "", COUNTIF($A$2:A5, A5))		4	#N/A	#N/A	#N/A	=COUNTBLANK(D2:D13)	5
a	a1	library	5			5	#N/A	#N/A	#N/A	=countif(B2:B13, "*")	12
a	a2	pen	6			6	#N/A	#N/A	#N/A	=countif(D2:D13, "*")	5
an	an1	apple	7			7	#N/A	#N/A	#N/A	=rows(A2:D13)*columns(A2:D13)	48
		Germany				8	#N/A	#N/A	#N/A	=counta(A2:D13)+countblank(A2:D13)	58
		England				9	#N/A	#N/A	#N/A
the	the2	librarian	10			10	#N/A	#N/A	#N/A
the	the3	forest	11			11	#N/A	#N/A	#N/A
an	an2	uncle	12

<colgroup><col style="width: 60px"><col width="60"><col width="60"><col width="60"><col width="268"><col width="198"><col width="60"><col width="60"><col width="60"><col width="60"><col width="228"><col width="60"></colgroup><tbody>
</tbody>

 

[sevas1994]

Formulas in B column

Formulas in C column

a

an

the

the

the1

book

1

=A2&IF(A2=""; ""; COUNTIF($A$2:A2; A2))

=IF(B2=""; ""; COUNTA($B$2:B2))

1

library

apple

book

total cells

48

=COLUMNS(A2:D13)*ROWS(A2:D13)

my

=A3&IF(A3=""; ""; COUNTIF($A$2:A3; A3))

=IF(B3=""; ""; COUNTA($B$2:B3))

2

pen

uncle

librarian

total cells

58

=COUNTA(A2:D13)+COUNTBLANK(A2:D13)

mine

=A4&IF(A4=""; ""; COUNTIF($A$2:A4; A4))

=IF(B4=""; ""; COUNTA($B$2:B4))

3

#N/A

#N/A

forest

Non-text values

12

=COUNTIF(A2:D13; "<>*")

your

=A5&IF(A5=""; ""; COUNTIF($A$2:A5; A5))

4

#N/A

#N/A

#N/A

Non-blank values in formula

12

=COUNTA(B2:B13)

a

a1

library

5

=A6&IF(A6=""; ""; COUNTIF($A$2:A6; A6))

5

#N/A

#N/A

#N/A

Blank cells in formula

5

=COUNTBLANK(B2:B13)

a

a2

pen

6

=A7&IF(A7=""; ""; COUNTIF($A$2:A7; A7))

6

#N/A

#N/A

#N/A

Numeric values

7

=COUNT(A2:D13)

an

an1

apple

7

=A8&IF(A8=""; ""; COUNTIF($A$2:A8; A8))

7

#N/A

#N/A

#N/A

Non-text values

12

{=SUM(IF(ISNONTEXT(A2:D13);1))}

Germany

8

#N/A

#N/A

#N/A

Text values

36

{=SUM(IF(ISTEXT(A2:D13);1))}

England

9

#N/A

#N/A

#N/A

Text values

36

=COUNTIF(A2:D13; "*")

the

the2

librarian

10

10

#N/A

#N/A

#N/A

N/A value

26

=COUNTIF(H1:J12; "#N/A")

the

the3

forest

11

11

#N/A

#N/A

#N/A

an

an2

uncle

12

<colgroup><col span="4"><col><col><col span="4"><col><col span="4"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

@sevas1994

Would you mind posting just the data and the expected results?

Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl
is also a method for posting an exhibit.<strike></strike>

 

[sheetspread]

If you're trying to exclude "" and true blanks from your count:

Excel 2010
	A	B	C	D	E	F
2	the	the1	book	1
3			my
4			mine
5			your
6	a	a1	library	5
7	a	a2	pen	6
8	an	an1	apple	7
9			Germany
10			England
11	the	the2	librarian	10
12	the	the3	forest	11
13	an	an2	uncle	12		33
14						15
Sheet16

Cell Formulas
Range	Formula
A3	=""
A4	=""
A5	=""
A9	=""
A10	=""
B3	=""
B4	=""
B5	=""
B9	=""
B10	=""
F13	=SUMPRODUCT(--(LEN(A2:D13)<>0))
F14	=SUMPRODUCT(--(LEN(A2:D13)=0))

is another way

 

[Aladin Akyurek]

If you're trying to exclude "" and true blanks from your count:

[...]

F13	=SUMPRODUCT(--(LEN(A2:D13)<>0))
F14	=SUMPRODUCT(--(LEN(A2:D13)=0))

<tbody>
</tbody>

is another way

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

=COUNTIFS(A2:D13,"")

would be tad faster in this type of text situations.

 

[sheetspread]

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

I get 26 though (because of the numbers in column D perhaps)

 

[Aladin Akyurek]

I get 26 though (because of the numbers in column D perhaps)

That would require expanding:

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

which might be still a good score qua performance.