Hi,

Data as follows:

A B C
ID1 YES YES
ID2 YES
ID3 YES
ID1
ID4 YES YES
ID2 YES
ID2 YES YES

What formula to be inserted in Column D to result the number of times an ID
in Column A repeats in case where either Column B or C shows "YES"

Desired result:

A B C D
ID1 YES YES 1
ID2 YES 3
ID3 YES 1
ID1 "" (Blank because B & C both are <"YES")
ID4 YES YES 1
ID2 YES 3
ID2 YES YES 3


--

Best Regards,
FARAZ A. QURESHI

Try this:

=IF(COUNTA(B1:C1),SUMPRODUCT(--(A$1:A$7=A1),--((B$1:B$7="yes")+(C$1:C$7="yes")0)),"")


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
Hi,

Data as follows:

A B C
ID1 YES YES
ID2 YES
ID3 YES
ID1
ID4 YES YES
ID2 YES
ID2 YES YES

What formula to be inserted in Column D to result the number of times an
ID
in Column A repeats in case where either Column B or C shows "YES"

Desired result:

A B C D
ID1 YES YES 1
ID2 YES 3
ID3 YES 1
ID1 "" (Blank because B & C both are <"YES")
ID4 YES YES 1
ID2 YES 3
ID2 YES YES 3


--

Best Regards,
FARAZ A. QURESHI

XCLent Biff,

But just found a problem. "Yes" is being reflected by a formula and so
Counta() also counts the "". How 2 overtake this problem?

--

Best Regards,
FARAZ A. QURESHI


"T. Valko" wrote:

Try this:

=IF(COUNTA(B1:C1),SUMPRODUCT(--(A$1:A$7=A1),--((B$1:B$7="yes")+(C$1:C$7="yes")0)),"")


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
Hi,

Data as follows:

A B C
ID1 YES YES
ID2 YES
ID3 YES
ID1
ID4 YES YES
ID2 YES
ID2 YES YES

What formula to be inserted in Column D to result the number of times an
ID
in Column A repeats in case where either Column B or C shows "YES"

Desired result:

A B C D
ID1 YES YES 1
ID2 YES 3
ID3 YES 1
ID1 "" (Blank because B & C both are <"YES")
ID4 YES YES 1
ID2 YES 3
ID2 YES YES 3


--

Best Regards,
FARAZ A. QURESHI

Does this modification to Biff's formula solve your problem?
=IF(COUNTIF(B1:C1,"yes"),SUMPRODUCT(--(A$1:A$7=A1),--((B$1:B$7="yes")+(C$1:C$7="yes")0)),"")
--
David Biddulph

"FARAZ QURESHI" wrote in message
...
XCLent Biff,

But just found a problem. "Yes" is being reflected by a formula and so
Counta() also counts the "". How 2 overtake this problem?

--

Best Regards,
FARAZ A. QURESHI


"T. Valko" wrote:

Try this:

=IF(COUNTA(B1:C1),SUMPRODUCT(--(A$1:A$7=A1),--((B$1:B$7="yes")+(C$1:C$7="yes")0)),"")


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
Hi,

Data as follows:

A B C
ID1 YES YES
ID2 YES
ID3 YES
ID1
ID4 YES YES
ID2 YES
ID2 YES YES

What formula to be inserted in Column D to result the number of times
an
ID
in Column A repeats in case where either Column B or C shows "YES"

Desired result:

A B C D
ID1 YES YES 1
ID2 YES 3
ID3 YES 1
ID1 "" (Blank because B & C both are <"YES")
ID4 YES YES 1
ID2 YES 3
ID2 YES YES 3


--

Best Regards,
FARAZ A. QURESHI