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 ?

Howdy. You don't need the extra column:

=SUMPRODUCT(($A$2:$A$10=$A$2)*($B$2:$B10="Y"))

Howdy. You don't need the extra column:

=SUMPRODUCT(($A$2:$A$10=$A$2)*($B$2:$B10="Y"))

Hi Shady,

I tried this with a little change to the formula. Following is the result I have got which deviates from my expectation.

Job-----Value--Occ
ABC2DX-- Y---0
ABC2DX-- Y---#N/A
ABC2DX-- N---#N/A
LKNJ12X- Y---#N/A
LKNJ12X- Y---#N/A
ABX1234- N---#N/A
ABX1234- N---#N/A
ABX1234- Y---#N/A
ABX125X- N---#N/A

The change in the formula is - the formula is on C2
=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B11="Y"))

I don't want to hard code A2, since this is summation is not aganist one job.

Want the count of "Y"s for each job.

Not sure what is the problem here. Can you please let me know the problem here ?

Can you post a sample spreadsheet? Then I will look at it. Are you saying that you will use the SUMPRODUCT in other workbooks? As long as each part is the same length (starting and ending cells) you will be okay. We can define a dynamic named range for each column and use those.

Hi Shady,
The change in the formula is - the formula is on C2
=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B11="Y"))

I don't want to hard code A2, since this is summation is not aganist one job.

Want the count of "Y"s for each job.

Not sure what is the problem here. Can you please let me know the problem here ?
Your problem is that you changed the length of column B to cell B11, but didn't change column A references. Thus, the formula will not work.

=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B11="Y"))

It should be this:

=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B11="Y"))

Now, if you want all of each, then make a unique list of the items in Column A, and put that unique list in column C, starting at C2, then in D2 put this formula:

=SUMPRODUCT(($A$2:$A$11=C2)*($B$2:$B11="Y"))

And copy this down as far as the unique list goes in column C. This will tell you how many of each.

Hi Shades, That was a typo from my side. Please see the attached spread sheet. Could you let me know what could be wrong here ?

Your problem is that in column B, you have a space before every letter. When you delete that space in each cell, then it will read correctly.

Note on attaachment: I don't have the ability to zip a file at home, and the board doesn't accept xls files, so I changed it to .doc. After you download, change the .doc back to .xls and then open with Excel.

Thank you so Much and sorry for not looking into these basic things...

Glad to help and that your problem has been resolved. Thanks for coming back with the result.