How do I count cells with specific value?

G

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
 
G

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

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

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

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

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Number of occurences of several texts in a range of cells 3
Really challenging lookup situation. 1
Nested IFs and multiples 7
Count number of rows with specific text 2
How can I count values in multiple non-consecutive cells? 1
I'd like to know how we can count coloured cells? 2
count nonblank cells 3
Count Common Denominator, but excluding "#value!" 4

Top