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

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

copy down as far as needed


"Cathy" wrote:

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

Try this in J20:

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

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

Hope this helps.

Pete

On Jul 2, 6:40*pm, Cathy wrote:
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

I cannot get this to work either.

"Pete_UK" wrote:

Try this in J20:

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

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

Hope this helps.

Pete

On Jul 2, 6:40 pm, Cathy wrote:
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

This does not work.

"Teethless mama" wrote:

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

copy down as far as needed


"Cathy" wrote:

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

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

On Jul 2, 10:48*pm, Cathy wrote:
I cannot get this to work either.



"Pete_UK" wrote:
Try this in J20:

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

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

Hope this helps.

Pete

On Jul 2, 6:40 pm, Cathy wrote:
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- Hide quoted text -

- Show quoted text -