I have a list of names from A8 thru A70. For each name I need to put in M8
thru M70 counters of how many occurences of those names from D146 thru D1465
I have. But only count the ones that have "1-Yes" in the corresponding J146
thru J1465.

I am trying to put this one in M8 but it does not work:
=SUMPRODUCT((D146:D1465=A8:A70)*(J146:J1465="1-Yes"))

Please help me to fix it. I need this very urgently.

Thank you.

Try:
=SUMPRODUCT((ISNUMBER(MATCH(D14:D1465,A8:A70,0))*( J14:J1465="1-Yes")))
Success? hit the YES below
--
Max
Singapore
---
"Vic" wrote:
I have a list of names from A8 thru A70. For each name I need to put in M8
thru M70 counters of how many occurences of those names from D146 thru D1465
I have. But only count the ones that have "1-Yes" in the corresponding J146
thru J1465.

I am trying to put this one in M8 but it does not work:
=SUMPRODUCT((D146:D1465=A8:A70)*(J146:J1465="1-Yes"))

Please help me to fix it. I need this very urgently.

Thank you.

You just need to delete ":A70" resulting in:

=SUMPRODUCT((D146:D1465=A8)*(J146:J1465="1-Yes"))

Tom


"Vic" wrote:

I have a list of names from A8 thru A70. For each name I need to put in M8
thru M70 counters of how many occurences of those names from D146 thru D1465
I have. But only count the ones that have "1-Yes" in the corresponding J146
thru J1465.

I am trying to put this one in M8 but it does not work:
=SUMPRODUCT((D146:D1465=A8:A70)*(J146:J1465="1-Yes"))

Please help me to fix it. I need this very urgently.

Thank you.

Better, use this and copy it down to cell M70:

=SUMPRODUCT(($D$146:$D$1465=A8)*($J$146:$J$1465="1-Yes"))

"tompl" wrote:

You just need to delete ":A70" resulting in:

=SUMPRODUCT((D146:D1465=A8)*(J146:J1465="1-Yes"))

Tom


"Vic" wrote:

I have a list of names from A8 thru A70. For each name I need to put in M8
thru M70 counters of how many occurences of those names from D146 thru D1465
I have. But only count the ones that have "1-Yes" in the corresponding J146
thru J1465.

I am trying to put this one in M8 but it does not work:
=SUMPRODUCT((D146:D1465=A8:A70)*(J146:J1465="1-Yes"))

Please help me to fix it. I need this very urgently.

Thank you.

I used this =SUMPRODUCT(($D$146:$D$1465=A8)*($J$146:$J$1465="1-Yes")) copied
that down from M8 thru M70, and I still get zeroes in M8 thru M70.
What is the fix for this?
Thanks

"tompl" wrote:

You just need to delete ":A70" resulting in:

=SUMPRODUCT((D146:D1465=A8)*(J146:J1465="1-Yes"))

Tom


"Vic" wrote:

I have a list of names from A8 thru A70. For each name I need to put in M8
thru M70 counters of how many occurences of those names from D146 thru D1465
I have. But only count the ones that have "1-Yes" in the corresponding J146
thru J1465.

I am trying to put this one in M8 but it does not work:
=SUMPRODUCT((D146:D1465=A8:A70)*(J146:J1465="1-Yes"))

Please help me to fix it. I need this very urgently.

Thank you.

If you get zeroes, that tells you that you haven't satisfied your
conditions.
If you are struggling with the debugging, put =D146=A$8 in one column, and
=J146="1-Yes" in another column. Copy those formulae down and see whether
your TRUE and FALSE results agree with what you expect. If you have strings
that look identical but aren't returning the expected result, look out for
spare spaces or non-breaking spaces or other non-printing characters. If
you think you've got "1-Yes" in column J, check whether =LEN(J146) returns
5.
--
David Biddulph


"Vic" wrote in message
...
I used this =SUMPRODUCT(($D$146:$D$1465=A8)*($J$146:$J$1465="1-Yes"))
copied
that down from M8 thru M70, and I still get zeroes in M8 thru M70.
What is the fix for this?
Thanks

"tompl" wrote:

You just need to delete ":A70" resulting in:

=SUMPRODUCT((D146:D1465=A8)*(J146:J1465="1-Yes"))

Tom


"Vic" wrote:

I have a list of names from A8 thru A70. For each name I need to put in
M8
thru M70 counters of how many occurences of those names from D146 thru
D1465
I have. But only count the ones that have "1-Yes" in the corresponding
J146
thru J1465.

I am trying to put this one in M8 but it does not work:
=SUMPRODUCT((D146:D1465=A8:A70)*(J146:J1465="1-Yes"))

Please help me to fix it. I need this very urgently.

Thank you.