Counting Formula

[Cathy]

On a worksheet name sales this is what I have


I need in Column J for the forluma to look at a worksheet name Parts whic
looks like this

C D E F
2 H-1 0 0 Yes
3 C-55 350 12
4 H-1 0 3 Yes
5 C-55 2 4 Yes
6 A-98 400 6 Yes

I need to formula on the sales worksheet to look the parts worksheet and if
cell I20 matches anything on Parts column C, it is going to give me the total
number of yes's and not count the blanks in column F

So I Sales worksheet would look like this -

I J
20 A-98 1
21 C-55 1
22 H-1 2
23
24


I appreciate your help.

Cathy

 

[Teethless mama]

=SUMPRODUCT(--($C$2:$C$6=I20),--($F$2:$F$6="Yes"))

copy down as far as needed

 

[Pete_UK]

Try this in J20:

=SUMPRODUCT((Parts!C2:C100=I20)*(Parts!F2:F100="Yes"))

Adjust the 100 (twice) if you have more rows in your Parts sheet.

Hope this helps.

Pete

 

[Cathy]

I cannot get this to work either.

Try this in J20:

=SUMPRODUCT((Parts!C2:C100=I20)*(Parts!F2:F100="Yes"))

Adjust the 100 (twice) if you have more rows in your Parts sheet.

Hope this helps.

Pete

 

[Cathy]

This does not work.

=SUMPRODUCT(--($C$2:$C$6=I20),--($F$2:$F$6="Yes"))

copy down as far as needed

 

[Pete_UK]

Did it return an error? Did it return a value that you did not expect?
If so, what did you expect, and why, and what did the formula return?
Did you type the formula exactly as given?

Please be more specific, so that I can try to diagnose what is wrong.

Pete