Counting formula results and text data while ignoring blank cells

[Darren_workforce]

Hello,

Column i has a macro that I grabbed online to count colored cells. For the purposes of this inquiry, I believe it's irrelevant but let me know if it will help. C:G results come from a replication formula (see C2 below reference for example). However, Column H are all manually data entered names with no formulas.

My formulas in K do not appear to be counting accurately. I've tried the below formulas and also attempted to use COUNTBLANK formulas but no matter what, it's not adding correctly. What I'd like is to have K tally up the number of names that are returned from both the IF(ISBLANK...) formulas as well as the data entered cells in H while at the same time, ignoring the blank cells that did not return a name. Once it adds up the number of names that appear, it then adds that total to the total from the above cell for a running total. K2:K4 are correct but Row 5 is where the calculation begins to go astray. Any help is greatly appreciated.

C2 formula:

=IF(ISBLANK(M7),"",M7)]

K2 formula:

=(COUNTA(C2:H2)-COUNTIF(C2:H2,""))

K3 formula:

=(COUNTA(C3:H3)-COUNTIF(C3:H3,""))+K2

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

1

Day

Date

Agents

Running Rep Count

Compliant %

returned results

should be:

2

M

5/6/2024

Haley

Debbie

Mary

Sarah

Charles

4

/

5

5

3

T

5/7/2024

Dan

Michelle

Max

Elizabeth

4

/

9

9

4

W

5/8/2024

Ryan

Robin

Brittany

Shelley

4

/

13

13

5

R

5/9/2024

Terrance

Jennifer

Whitney

Alice

4

/

16

17

6

F

5/10/2024

Gary

Alex

Kim

Wanda

Lisa

4

/

20

22

 

[Scott Huish]

K2: =COUNTIF(C2:H2,"?*")
K3: =COUNTIF(C3:H3,"?*")+K2
Copy down.

COUNTIF should work because all this does is count cells that have at least 1 character in them, thus eliminating blanks.

 

[Scott Huish]

How is this not what you want?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Day	Date	Agents	Running Rep Count	Compliant %						returned results
2	M	5/6/2024	Haley	Debbie	Mary	Sarah		Charles	4	/	5
3	T	5/7/2024	Dan	Michelle	Max			Elizabeth	4	/	9
4	W	5/8/2024	Ryan	Robin	Brittany			Shelley	4	/	13
5	R	5/9/2024	Terrance	Jennifer	Whitney	Alice			4	/	17
6	F	5/10/2024	Gary	Alex	Kim	Wanda	Lisa		4	/	22
Sheet1

Cell Formulas
Range	Formula
K2	K2	=COUNTIF(C2:H2,"?*")
K3:K6	K3	=COUNTIF(C3:H3,"?*")+K2

 

[Scott Huish]

Thank you. I tried that but if there's no data, it was populating a '0' so I tried to set it up to appear blank if that was the case.

That's not possible.
=M7 would do what you're saying.
=T(M7) would not.

Book1
	M	N
7
Sheet1

Cell Formulas
Range	Formula
N7	N7	=T(M7)

 

[shift-del]

With all 5 cells in the row "blank",

Is this a real life scenario? I doubt it.

 

[Darren_workforce]

COUNTIF should work because all this does is count cells that have at least 1 character in them, thus eliminating blanks.

The '0' is still populating in the cell if the reference cell is blank. However, conditional formatting made those disappear and it didn't affect the overall formula. Apologies. I had COUNTIF in my original formula and I thought it was referencing merely the actual blank cells in my data and not the blank results of a formula. It does appear that works and does what I need in a simpler form while also accurately calculating blank rows. Thank you for sticking with me on this.

 

[Scott Huish]

Easier way to hide the 0 is to just format the cells as a custom number format of 0;;

 

[Darren_workforce]

Easier way to hide the 0 is to just format the cells as a custom number format of 0;;

ok good to know, thank you!