Sumproduct/formula help needed

[William Rivero]

___A____B___C___DD___EE___FF
1__Y________Y_________Y____Y
2_______Y
3_______Y_____________Y____Y
4_______Y___Y_____Y____Y
5__________________________Y

Problem: I want to count rows in columns A:C with more than 1 "Y" (should return a value of 2 in this case) and do the same with columns D:H (should return a value of 3).

Problem2: Also I want to count rows with >1 "Y" in A:C AND D:H (should get 2 [for rows 1 and 4]) in combination.

Thank you in advance. I know I'm missing something somewhere. Tried a very very long equation but its inaccurate.

 

[T. Valko]

___A____B___C___DD___EE___FF
1__Y________Y_________Y____Y
2_______Y
3_______Y_____________Y____Y
4_______Y___Y_____Y____Y
5__________________________Y

Problem: I want to count rows in columns A:C with more than 1 "Y" (should return a value of 2 in this case) and do the same with columns D:H (should return a value of 3).

Problem2: Also I want to count rows with >1 "Y" in A:C AND D:H (should get 2 [for rows 1 and 4]) in combination.

Thank you in advance. I know I'm missing something somewhere. Tried a very very long equation but its inaccurate.

Try these...

Problem 1:

=SUMPRODUCT(--(MMULT(--(A2:C10="y"),{1;1;1})>1))

Problem 2:

=SUMPRODUCT(--(MMULT(--(A2:C10="y"),{1;1;1})>1),--(MMULT(--(D2:H10="y"),{1;1;1;1;1})>1))

 

[William Rivero]

Simply brilliant!

Thanks!

 

[T. Valko]

Simply brilliant!

Thanks!

You're welcome. Thanks for the feedback!