How do I count cells with specific value?

[Guntars]

Hello every one,
I am still trying to wrap my mind around array formula, this is my challenge
now.
How do I count cells with specific value?
Example:
___A____B__C__D__E__F__E
1_ONE___X_____Y_____Z___
2_TWO_____Y______Z__Z__
3_TWO___Z_____Y____X__Z
4_ONE___Y__Y_______Z___X
5_TWO_________X_______X
6_TWO_____X______Y____Y
7_ONE___X______________X
8_ONE_____X____Z__X____Y
In a column A1:A8 I got some ONE’s and TWO’s, how do I count, how many “Yâ€
there are in ONE rows, range B1:E8. The way I see it, it is calling for array
formula.
Please help!
Thank you,
Guntars

 

[Ashish Mathur]

Hi,

you can try this

=SUMPRODUCT((A1:A8=A10)*($B$1:$F$8=B10))

A10 holds One and B10 holds Y

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Guntars]

Thank you all, for all your suggestions and solutions, I think I am getting
better at array formulas, this is what I come up withâ€
{=SUMPRODUCT(--(A1:A8="ONE")*(B1:F8="Y"))}

 

[T. Valko]

{=SUMPRODUCT(--(A1:A8="ONE")*(B1:F8="Y"))}

No need to array enter. Just a normal enter will do. Also, no need for the
"--".

=SUMPRODUCT((A1:A8="ONE")*(B1:F8="Y"))

 

[Guntars]

Thank you T. Valko. That is what I am looking for, simpler shorter formula. I
do understand that almost always there is more than one way to accomplish
desired result. The key is, to do that as simple as possible!
Thank you
Guntars

 

[T. Valko]

See this for more detailed info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html