Formula Required To count names and Ticks

[Arts]

Hi all

I have an attendance tracker for which I need to count the individual who has attended which is in the form of a tick but if this individual sends a replacement I then need it to count the name (each entry represents a month so the below would be showing Jan, Feb, Mar).

But if the regular attendee can't attend and a representative can't attend this is shown as a cross and do not need this counted. example below: For the below I would need the count to be 2.

The reason for this is to get an average for the year for attendance. I got as far as adding a countif(s) to count the tick but wasn't sure how to count a name.

Many thanks

 

[Snakehips]

@Arts Maybe try Sumproduct along these lines?

Book2
	B	C	D	E	F	G	H	I	J	K
1	Count		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	5		x	Tom White	Y	x	Y	Fred Smith	Y
Sheet2

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT((D2:O2 ="Y")+(LEN(D2:O2)>2))

 

[Arts]

@Arts Maybe try Sumproduct along these lines?

Book2
	B	C	D	E	F	G	H	I	J	K
1	Count		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	5		x	Tom White	Y	x	Y	Fred Smith	Y
Sheet2

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT((D2:O2 ="Y")+(LEN(D2:O2)>2))

Thank you this does the trick!! I'm not familiar with this function at all but will look into it. Would you be kind enough to explain why this would be the "better" option as I was going down the route of multiple countifs. I also noted that at first I only used the one parenthesis when doing sum product and this gave me an error are two always required when using this function? Sorry for all the questions wanting to get as much knowledge as possible.

 

[Snakehips]

@Arts You could do it using Countif.

Book2
	B	C	D	E	F	G	H	I	J	K
1	Count		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	5		x	Tom White	Y	x	Y	Fred Smith	Y
Sheet2

Cell Formulas
Range	Formula
B2	B2	=COUNTIF(D2:O2,"Y")+COUNTIF(D2:O2,"??*")

Re Sumproduct: If you Google you will see that it does what it says on the tin and sums the product of a series of similar sized arrays.
You can use mathematical operators *, /, +, - If purely numeric data then you can get a result from a single array.
When you use it for testing an array against conditions, you get resulting TRUE or FALSE arrays. If it's just a single array then it will not resolve to a number if it is not subject to mathematical operation eg with a second array or value. Typically, the * operator acts a AND and + acts as OR. You have a lot of scope as to the functions that you can use to test your conditions. A lot of which you could not test when using other functions, eg Countif.
However, Google is your friend.
Hope that helps.

 

[Arts]

@Arts You could do it using Countif.

Book2
	B	C	D	E	F	G	H	I	J	K
1	Count		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	5		x	Tom White	Y	x	Y	Fred Smith	Y
Sheet2

Cell Formulas
Range	Formula
B2	B2	=COUNTIF(D2:O2,"Y")+COUNTIF(D2:O2,"??*")

Re Sumproduct: If you Google you will see that it does what it says on the tin and sums the product of a series of similar sized arrays.
You can use mathematical operators *, /, +, - If purely numeric data then you can get a result from a single array.
When you use it for testing an array against conditions, you get resulting TRUE or FALSE arrays. If it's just a single array then it will not resolve to a number if it is not subject to mathematical operation eg with a second array or value. Typically, the * operator acts a AND and + acts as OR. You have a lot of scope as to the functions that you can use to test your conditions. A lot of which you could not test when using other functions, eg Countif.
However, Google is your friend.
Hope that helps.

I was going down the google route but thought if you could give me the "highlights" as you have done would be very helpful and that it is. Thank you so much for this.