| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if usage based on two ranges NO sum required
COL_A COL_B COL_C
2 1 Yes 2 1 Yes 2 2 Yes 1 3 No I need to have COL_C say yes if any value in COL_B = any value in COL_A. I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work. :( I read the formula as "if range a2 to a5 = B2, then say yes. . . Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if usage based on two ranges NO sum required
Try this:
=IF(COUNTIF($A$2:$A$5,B2)0,"Yes","No") HTH, Elkar "ross" wrote: COL_A COL_B COL_C 2 1 Yes 2 1 Yes 2 2 Yes 1 3 No I need to have COL_C say yes if any value in COL_B = any value in COL_A. I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work. :( I read the formula as "if range a2 to a5 = B2, then say yes. . . Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if usage based on two ranges NO sum required
On Oct 30, 2:43 pm, ross wrote:
COL_A COL_B COL_C 2 1 Yes 2 1 Yes 2 2 Yes 1 3 No I need to have COL_C say yes if any value in COL_B = any value in COL_A. I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work. :( I read the formula as "if range a2 to a5 = B2, then say yes. . . Any ideas? Try... C2, copied down: =IF(ISNUMBER(MATCH(B2,$A$2:$A$5,0)),"Yes","No") Hope this helps! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if usage based on two ranges NO sum required
=IF(COUNTIF($A$2:$A$5,B2),"yes","no")
"ross" wrote: COL_A COL_B COL_C 2 1 Yes 2 1 Yes 2 2 Yes 1 3 No I need to have COL_C say yes if any value in COL_B = any value in COL_A. I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work. :( I read the formula as "if range a2 to a5 = B2, then say yes. . . Any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if usage based on two ranges NO sum required
Thanks, works great.
How do I read this? I see the range ref and check against B2. . . then??? I figure if I understand what it says, I can move towards my own excel solutions. Ross "Elkar" wrote: Try this: =IF(COUNTIF($A$2:$A$5,B2)0,"Yes","No") HTH, Elkar "ross" wrote: COL_A COL_B COL_C 2 1 Yes 2 1 Yes 2 2 Yes 1 3 No I need to have COL_C say yes if any value in COL_B = any value in COL_A. I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work. :( I read the formula as "if range a2 to a5 = B2, then say yes. . . Any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if usage based on two ranges NO sum required
The COUNTIF function takes each value within the range (A2:A5) and checks to
see if the condition is met (=B2). If so, it increments (or counts) by 1. So basically: Is A2 = B2? Is A3 = B2? Is A4 = B2? Is A5 = B2? For each of the values that are true, COUNTIF would increment by 1 and return that value (in this example, it would be a number from 0 to 4). The IF portion then takes the result of the COUNTIF and checks to see if it is greater than 0. If so, "Yes" is returned. If not, "No" is returned. Does that help? Elkar "ross" wrote: Thanks, works great. How do I read this? I see the range ref and check against B2. . . then??? I figure if I understand what it says, I can move towards my own excel solutions. Ross "Elkar" wrote: Try this: =IF(COUNTIF($A$2:$A$5,B2)0,"Yes","No") HTH, Elkar "ross" wrote: COL_A COL_B COL_C 2 1 Yes 2 1 Yes 2 2 Yes 1 3 No I need to have COL_C say yes if any value in COL_B = any value in COL_A. I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work. :( I read the formula as "if range a2 to a5 = B2, then say yes. . . Any ideas? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if usage based on two ranges NO sum required
Excellent, that will help in many ways!
Many thanks. :-) Ross A "Elkar" wrote: The COUNTIF function takes each value within the range (A2:A5) and checks to see if the condition is met (=B2). If so, it increments (or counts) by 1. So basically: Is A2 = B2? Is A3 = B2? Is A4 = B2? Is A5 = B2? For each of the values that are true, COUNTIF would increment by 1 and return that value (in this example, it would be a number from 0 to 4). The IF portion then takes the result of the COUNTIF and checks to see if it is greater than 0. If so, "Yes" is returned. If not, "No" is returned. Does that help? Elkar "ross" wrote: Thanks, works great. How do I read this? I see the range ref and check against B2. . . then??? I figure if I understand what it says, I can move towards my own excel solutions. Ross "Elkar" wrote: Try this: =IF(COUNTIF($A$2:$A$5,B2)0,"Yes","No") HTH, Elkar "ross" wrote: COL_A COL_B COL_C 2 1 Yes 2 1 Yes 2 2 Yes 1 3 No I need to have COL_C say yes if any value in COL_B = any value in COL_A. I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work. :( I read the formula as "if range a2 to a5 = B2, then say yes. . . Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate date when funds run out based on current usage | Excel Discussion (Misc queries) | |||
| Multiple If Then Statement based on ranges | Excel Worksheet Functions | |||
| Overview usage named ranges | Excel Discussion (Misc queries) | |||
| is it possible to make certain cells required based on criteria | Excel Worksheet Functions | |||
| [Shift] + [Arrow] key usage with chart ranges | Excel Worksheet Functions | |||
Linear Mode
