|
Sort of like SUMIF and COUNTIF - but return a conditional value
Sheet_1 has the following in columns A & B:
001 Y 003 N 002 Y 002 N 001 Y Column A of Sheet_2 has sorted, unique values from col A of Sheet_1: 001 002 003 I'd like a formula for Column B of Sheet_2, that finds every instance of the value in column A and compares its Column B values in Sheet_1, such that if all are Y it returns Y, if all are N it returns N, but if some are Y and some are N it returns Y. The expected results would be: 001 Y 002 Y 003 N Any help is greatly appreciated! -- Thanks |
Sort of like SUMIF and COUNTIF - but return a conditional value
=IF(COUNTIF(Sheet1!$A$1:$A$5,B1)=SUMPRODUCT(--(Sheet1!$A$1:$A$5=B1),--(Sheet1!$B$1:$B$5="N")),"N","Y")
and fill down "hr38581" wrote: Sheet_1 has the following in columns A & B: 001 Y 003 N 002 Y 002 N 001 Y Column A of Sheet_2 has sorted, unique values from col A of Sheet_1: 001 002 003 I'd like a formula for Column B of Sheet_2, that finds every instance of the value in column A and compares its Column B values in Sheet_1, such that if all are Y it returns Y, if all are N it returns N, but if some are Y and some are N it returns Y. The expected results would be: 001 Y 002 Y 003 N Any help is greatly appreciated! -- Thanks |
Sort of like SUMIF and COUNTIF - but return a conditional valu
This formula yielded a circular reference. Since the formula is in
Sheet2!B1, I changed both instances of B1 to A1. =IF(COUNTIF(Sheet1!$A$1:$A$5,A1)=SUMPRODUCT(--(Sheet1!$A$1:$A$5=A1),--(Sheet1!$B$1:$B$5="N")),"N","Y") And it looks like I'm getting the correct results! Thank you very much!!! -- Thanks "Bob Umlas, Excel MVP" wrote: =IF(COUNTIF(Sheet1!$A$1:$A$5,B1)=SUMPRODUCT(--(Sheet1!$A$1:$A$5=B1),--(Sheet1!$B$1:$B$5="N")),"N","Y") and fill down "hr38581" wrote: Sheet_1 has the following in columns A & B: 001 Y 003 N 002 Y 002 N 001 Y Column A of Sheet_2 has sorted, unique values from col A of Sheet_1: 001 002 003 I'd like a formula for Column B of Sheet_2, that finds every instance of the value in column A and compares its Column B values in Sheet_1, such that if all are Y it returns Y, if all are N it returns N, but if some are Y and some are N it returns Y. The expected results would be: 001 Y 002 Y 003 N Any help is greatly appreciated! -- Thanks |
| All times are GMT +1. The time now is 05:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com