How do I count cells with specific value?
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 |
How do I count cells with specific value?
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 |
How do I count cells with specific value?
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"))} |
How do I count cells with specific value?
{=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"))} |
How do I count cells with specific value?
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"))} |
How do I count cells with specific value?
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"))} |
All times are GMT +1. The time now is 10:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com