CONDITIONAL SUMMING (URGENT)
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 |
CONDITIONAL SUMMING (URGENT)
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 |
CONDITIONAL SUMMING (URGENT)
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 |
CONDITIONAL SUMMING (URGENT)
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 |
All times are GMT +1. The time now is 11:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com