Following is the table I have with me.
I need to know how many "Y"s are there for each job. For ex, the job ABC2DX has 2 "Y"s and so on..
Job-----Value
ABC2DX-- Y
ABC2DX-- Y
ABC2DX-- N
LKNJ12X- Y
LKNJ12X- Y
ABX1234- N
ABX1234- N
ABX1234- Y
ABX125X- N
To achieve the objective,
I have added a temporaray column C called "count" and populated with "1"s. Now I have inserted the following formula on the "D" column.
Job-----Value---Count
ABC2DX-- Y------1
ABC2DX-- Y------1
ABC2DX-- N------1
LKNJ12X- Y------1
LKNJ12X- Y------1
ABX1234- N------1
ABX1234- N------1
ABX1234- Y------1
ABX125X- N------1
=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10="Y")*($C$2 :$C$10))
I expected the following:
Job-----Value---Count-ocuu
ABC2DX-- Y------1-----2
ABC2DX-- Y------1-----2
ABC2DX-- N------1-----2
LKNJ12X- Y------1-----2
LKNJ12X- Y------1-----2
ABX1234- N------1-----1
ABX1234- N------1-----1
ABX1234- Y------1-----1
ABX125X- N------1-----0
But I got the following:
ob-----Value---Count-ocuu
ABC2DX-- Y------1-----0
ABC2DX-- Y------1-----0
ABC2DX-- N------1-----0
LKNJ12X- Y------1-----0
LKNJ12X- Y------1-----0
ABX1234- N------1-----0
ABX1234- N------1-----0
ABX1234- Y------1-----0
ABX125X- N------1-----0
Could some one let me know whats the problem with my formula ?
I need to know how many "Y"s are there for each job. For ex, the job ABC2DX has 2 "Y"s and so on..
Job-----Value
ABC2DX-- Y
ABC2DX-- Y
ABC2DX-- N
LKNJ12X- Y
LKNJ12X- Y
ABX1234- N
ABX1234- N
ABX1234- Y
ABX125X- N
To achieve the objective,
I have added a temporaray column C called "count" and populated with "1"s. Now I have inserted the following formula on the "D" column.
Job-----Value---Count
ABC2DX-- Y------1
ABC2DX-- Y------1
ABC2DX-- N------1
LKNJ12X- Y------1
LKNJ12X- Y------1
ABX1234- N------1
ABX1234- N------1
ABX1234- Y------1
ABX125X- N------1
=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10="Y")*($C$2 :$C$10))
I expected the following:
Job-----Value---Count-ocuu
ABC2DX-- Y------1-----2
ABC2DX-- Y------1-----2
ABC2DX-- N------1-----2
LKNJ12X- Y------1-----2
LKNJ12X- Y------1-----2
ABX1234- N------1-----1
ABX1234- N------1-----1
ABX1234- Y------1-----1
ABX125X- N------1-----0
But I got the following:
ob-----Value---Count-ocuu
ABC2DX-- Y------1-----0
ABC2DX-- Y------1-----0
ABC2DX-- N------1-----0
LKNJ12X- Y------1-----0
LKNJ12X- Y------1-----0
ABX1234- N------1-----0
ABX1234- N------1-----0
ABX1234- Y------1-----0
ABX125X- N------1-----0
Could some one let me know whats the problem with my formula ?