Counting a Distinct Absent Occurance

[Stephen_IV]

Good afternoon

I have been using this formula to count absence occurance but I am trying to add a variable to it where I just want to count all of the "PI" occurances. Can someone please help. Thanks in advance Stephen!

=SUMPRODUCT(--($A2:$L2<>$B2:$M2),--($B2:$M2<>""))+($A2<>"")

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	8/25	8/26	8/27	8/30	8/31	9/1	9/2	9/3	9/7	9/8	9/10	9/13	9/14		Looking for
2		PI				PI	PI			PD				3	2
3									PI					1	1
4														0	0
5					PDV				FMLA					2	0
6														0	0
7			FMLA					FMLA	FMLA		PI		FMLA	4	1
Sheet1

 

[Ron Coderre]

Using your posted sample...Try this ARRAY FORMULA, completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER):
N2: =COUNT(1/FREQUENCY(IF((A2:M2="PI"),COLUMN(A2:M2)),IF((A2:M2<>"PI"),COLUMN(A2:M2))))

Copy N2 and paste into N3:N7

Using the sample data, these are the formula results:
N2: 2
N3: 1
N4: 0
N5: 0
N6: 0
N7: 1

Is that something you can work with?

 

[Matty]

Hi,

Try:

=SUM(IF(FREQUENCY(IF(A2:M2="PI",COLUMN(A2:M2)),IF(A2:M2<>"PI",COLUMN(A2:M2))),1))

In N2, committed with CTRL+SHIFT+ENTER and then copied down.

Matty

 

[Ron Coderre]

After some experimentation...

Here's a regular formula (completed by just pressing ENTER) that returns the same values
N2: =COUNT(INDEX(1/(B2:M2="PI")/(A2:L2<>"PI")*COLUMN(A2:L2),0))

Copy that formula down through N7

EDITED TO INCLUDE THIS COMMENT:
Flaw: That formula does not recognize a "PI" value in Col_A.
That could possibly be mitigated by having the data start in Col_B or
by inserting a column before the data and having the formula reference that preceding column.
Example:
O2: =COUNT(INDEX(1/(B2:N2="pi")/(A2:M2<>"PI")*COLUMN(B2:M2),0))

 

[Ron Coderre]

Actually, referenced range would have to extend before and after the actual data range by one column on either side:
P2: =COUNT(INDEX(1/(B2:O2="PI")/(A2:N2<>"PI")*COLUMN(B2:O2),0))

 

[Stephen_IV]

Beautiful Thanks Matty and Ron,

Ron great formula like the INDEX(1/ approach! Thank you both again!