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

 

[shift-del]

Hello. Try this.

Mappe6
	K	L
1	returned results	should be:
2	5	5
3	9	9
4	13	13
5	17	17
6	22	22
Tabelle1

Cell Formulas
Range	Formula
K2	K2	=COUNTA(TEXTSPLIT(TEXTJOIN(" ",TRUE,C2:H2)," "))
K3:K6	K3	=COUNTA(TEXTSPLIT(TEXTJOIN(" ",TRUE,C3:H3)," "))+K2

 

[Scott Huish]

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

 

[Scott Huish]

Your column C formula could also be just this:
=T(M7)

 

[Darren_workforce]

Your column C formula could also be just this:
=T(M7)

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.

 

[Darren_workforce]

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

There are different names so I'm not sure COUNTIF would work for what I need it for.

 

[Darren_workforce]

Hello. Try this.

Mappe6
	K	L
1	returned results	should be:
2	5	5
3	9	9
4	13	13
5	17	17
6	22	22
Tabelle1

Cell Formulas
Range	Formula
K2	K2	=COUNTA(TEXTSPLIT(TEXTJOIN(" ",TRUE,C2:H2)," "))
K3:K6	K3	=COUNTA(TEXTSPLIT(TEXTJOIN(" ",TRUE,C3:H3)," "))+K2

This appeared to work properly for a couple rows but then at row 9, it added an extra item even though there was none. After breaking down the formula, it appears because I added last initials on a couple names (same first names) it was creating a special count item for the initial. By removing the space between the first name and last initial, this formula works exactly as desired. Is there a way to take into account for the space if that is left in there?

 

[shift-del]

Replace the " " with a character that is not part of a text entry.

=COUNTA(TEXTSPLIT(TEXTJOIN("#",TRUE,C2:H2),"#"))

 

[Darren_workforce]

Replace the " " with a character that is not part of a text entry.

=COUNTA(TEXTSPLIT(TEXTJOIN("#",TRUE,C2:H2),"#"))

OK simple enough! That takes care of everything I was looking to do. Thank you very much for your help on this request!!

 

[Darren_workforce]

Replace the " " with a character that is not part of a text entry.

=COUNTA(TEXTSPLIT(TEXTJOIN("#",TRUE,C2:H2),"#"))

In theory, I may have spoken too soon. I generated a blank row using the

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

populating the row and then

=COUNTA(TEXTSPLIT(TEXTJOIN("#",TRUE,C2:H2),"#"))

at the end to count up everything.

With all 5 cells in the row "blank", the formula is still providing a result of '1' instead of '0'. Would you know why?