| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| function question (sumif countif conditional) | Excel Worksheet Functions | |||
| CountIF or SumIF | Excel Discussion (Misc queries) | |||
| Conditional Summing (Sumif? Countif?) | Excel Worksheet Functions | |||
| {} SumIf and CountIf | Excel Worksheet Functions | |||
| using sumif & countif to sort multiple cells | Excel Worksheet Functions | |||
Hybrid Mode
