Countif half

[scamsel]

Hi, I am building a simple vacation tracker and in each row are peoples days off, indicated with a letter. For example, a 'P' in a cell means that person a personal day that day. (and i use other letters for other types of days off)

But at the end of the row i'm using countif formula to count if the cell is populated with that letter then to count it. However, i have situations where a person may have taken a partial day off. for example ".5P".

What would be a simple way to have the formula result in 1.5 days off in a scenario as below:

many thanks in advance.

 

[kweaver]

If you used XL2BB to illustrate your example, I could have used more data. With an abbreviated set, see if you can use this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun		PPTO
3								.5P	P			.25P				1.75
4			P							P						2
5				P	P		.5P									2.5
Sheet15

Cell Formulas
Range	Formula
P3:P5	P3	=SUM(1*SUBSTITUTE(IF(LEN(A3:N3)=1,1&A3:N3,A3:N3),"P",""))

 

[Peter_SSs]

@kweaver
I don't think that you have taken account of ..

(and i use other letters for other types of days off)

@scamsel
I'm wondering if this is it? Are we just trying to evaluate the "P" days?

21 05 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun		PPTO
3				S		H		.5P	P			.25P				1.75
4			P				.5H			P						2
5				P	P		.5P									2.5
6			P	P	P	P	P	P	P	P	P					9
Sheet2 (2)

Cell Formulas
Range	Formula
P3:P6	P3	=SUM(SUBSTITUTE(FILTER(A3:N3,RIGHT(A3:N3,1)="P"),"P",0)+0)+COUNTIF(A3:N3,"P")

 

[kweaver]

@kweaver
I don't think that you have taken account of ..


@scamsel
I'm wondering if this is it? Are we just trying to evaluate the "P" days?

21 05 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun		PPTO
3				S		H		.5P	P			.25P				1.75
4			P				.5H			P						2
5				P	P		.5P									2.5
6			P	P	P	P	P	P	P	P	P					9
Sheet2 (2)

Cell Formulas
Range	Formula
P3:P6	P3	=SUM(SUBSTITUTE(FILTER(A3:N3,RIGHT(A3:N3,1)="P"),"P",0)+0)+COUNTIF(A3:N3,"P")

You're right, Peter...I was just addressing the P.

 

[scamsel]

This is amazing thank yall. kweeaver, sorry about not posting it via XL2BB. and Peter you are right, i am using different letters so that works! thanks a lot. i do get a #calc! error on some rows with no letters, is the best way to correct that with a iferror in front of the formula?

 

[kweaver]

You could wrap an "IFERROR" around Peter's solution.

 

[Fluff]

Rather than using iferror, filter has it's own error handling so you can use this tweak to Peter's formula

=SUM(SUBSTITUTE(FILTER(A3:N3,RIGHT(A3:N3,1)="P",0),"P",0)+0)+COUNTIF(A3:N3,"P")

 

[Peter_SSs]

Rather than using iferror, filter has it's own error handling so you can use this tweak to Peter's formula

=SUM(SUBSTITUTE(FILTER(A3:N3,RIGHT(A3:N3,1)="P",0),"P",0)+0)+COUNTIF(A3:N3,"P")

If somebody happened to enter "1P" instead of just "P" for a full day off, that would give an incorrect result, as would my original suggestion. If that was possible then this alternative may be safer

=SUM(IF(RIGHT(A3:N3,1)="P",SUBSTITUTE(A3:N3,"P",IF(A3:N3="P",1,""))+0))

.. or, assuming any non-blank cells in the range contain a letter code

=SUM(IFERROR(SUBSTITUTE(A3:N3,"P",IF(A3:N3="P",1,""))+0,0))