Counting rows with 3 columns

[Guest]

Hi,

I have three colums and I need to perform some counting but can't quite get
it right. Each column could possibly contain a single character, 'Y' or
nothing at all. I need to count the rows where the character 'Y' is present
in at least one column. In other words, if a 'Y' occurs anywhere in a row, I
want to count that row. Thanks in advance!

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--NOT(ISERR(SEARCH("Y",A1:A100&B1:B100&C1:C100))))

 

[Domenic]

Assuming that Columns A, B, and C contain your data, try...

=SUMPRODUCT(--(COUNTIF(OFFSET(A1:C10,ROW(A1:C10)-MIN(ROW(A1:C9)),0,1),"Y"
)>0))

....or the following formulas I picked up from Daniel M....

=SUMPRODUCT(--(MMULT(--(A1:C10="Y"),{1;1;1})>0))

....confirmed with just ENTER, and

=SUMPRODUCT(--(MMULT(--(A1:C10="Y"),TRANSPOSE(COLUMN(A1:C10))*0+1)>0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Domenic]

Correction...

=SUM(--(MMULT(--(A1:C10="Y"),TRANSPOSE(COLUMN(A1:C10))*0+1)>0))

....confirmed with CONTROL+SHIFT+ENTER.

 

[Guest]

I think this'll do what you're asking:
=SUMPRODUCT(--(LEFT(A1:A100&B1:B100&C1:C100,1)="Y"))

Regards,
Ron