| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula
A4=IF(COUNTIF(A1:AS1,1)=2,1,"")
The above formula searches a range of cells looking for the number 1 occurring 2 times and either returning 1 or leaving the cell blank. How could the formula be changed to only do the search if cell A1 was equal to the number 1? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
=if(A1=1,true result, false result)
"Cliff" wrote: A4=IF(COUNTIF(A1:AS1,1)=2,1,"") The above formula searches a range of cells looking for the number 1 occurring 2 times and either returning 1 or leaving the cell blank. How could the formula be changed to only do the search if cell A1 was equal to the number 1? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
Cliff, =IF(A1=1,IF(COUNTIF(A1:AS1,1)=2,1,""),"") HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=529598 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
Thank you, it worked great.
One additional request, how could this one formula be changed to include the additional range of cells at A6:E6 with the original range of A1:AS1? "SteveG" wrote: Cliff, =IF(A1=1,IF(COUNTIF(A1:AS1,1)=2,1,""),"") HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=529598 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
So you want to COUNTIF A1:AS1 and A6:E6 if they are equal to 1 and add them together to see if the sum = 2? =IF(A1=1,IF(COUNTIF(A1:AS1,1)+COUNTIF(A6:E6,1)=2,1 ,""),"") If you want it to look to see if either range's count of the number 1 returns a value of 2 then, =IF(A1=1,IF(OR(COUNTIF(A1:AS1,1)=2,COUNTIF(A6:E6,1 )=2),1,""),"") HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=529598 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
Thanks, the first one is what I needed.
"SteveG" wrote: So you want to COUNTIF A1:AS1 and A6:E6 if they are equal to 1 and add them together to see if the sum = 2? =IF(A1=1,IF(COUNTIF(A1:AS1,1)+COUNTIF(A6:E6,1)=2,1 ,""),"") If you want it to look to see if either range's count of the number 1 returns a value of 2 then, =IF(A1=1,IF(OR(COUNTIF(A1:AS1,1)=2,COUNTIF(A6:E6,1 )=2),1,""),"") HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=529598 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
Glad I could help. Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=529598 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| 2 Nesting questions | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Hybrid Mode
