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 ONEs and TWOs, 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

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" wrote in message
...
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 ONEs and TWOs, 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

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"))}

{=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"))

--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...
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"))}

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" wrote:

{=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"))

--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...
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"))}

See this for more detailed info on SUMPRODUCT:

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

--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...
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" wrote:

{=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"))

--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...
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"))}