Results 1 to 4 of 4
Thread: Nested If statements

20141115, 23:44 #1
 Join Date
 Oct 2014
 Posts
 21
 Thanks
 16
 Thanked 1 Time in 1 Post
Nested If statements
I have a spreadsheet that I use the following formula in cell A1.
=IF(B1=1,COUNTIF(C1:C5,"X"),IF(B1=2,COUNTIF(C1:C10 ,"X"),IF(B1=3,COUNTIF(C1:C15,"X"),IF(B1=4,COUNTIF( C1:C20,"X")))))
The formula uses the value in B1 to determine the range to count "X"s in column C. The formula works but I will need to continue to expand the ranges over time. There must be another way other than nested IFs but I can't find it. I cannot use a macro due to restrictions placed by our IT dept at work.
Any help would be most appreciated.
Thx
Nicole

20141116, 00:08 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,982
 Thanks
 157
 Thanked 773 Times in 705 Posts
Nicole,
You could use the following formula:
=COUNTIF(CHOOSE(B1,C1:C5,C1:C10,C1:C15,C1:C20),"X" )
Choose1.png
HTH,
Maud

The Following User Says Thank You to Maudibe For This Useful Post:
Nicole545 (20141116)

20141116, 06:30 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,954
 Thanks
 422
 Thanked 1,606 Times in 1,450 Posts
Nichole,
If you only have X or blank in col C you could simplify Maud's formula to:
Code:=COUNTA(CHOOSE(B1,C1:C5,C1:C10,C1:C15,C1:C20))
Nicole1.JPG
Then use:
Code:=COUNTA(INDIRECT(VLOOKUP($B$1,CountRng,2)))
Code:=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A$2:$A$10002),2)
Another method (if the added ranges keep going up by 5 rows each)
This method requires a "helper cell"
Code:Cell Formula A1 =COUNTA(INDIRECT($A$2)) A2 =CONCATENATE("C1:C",B1*5)
Test File: Nichole.xlsx
HTHLast edited by RetiredGeek; 20141116 at 06:36.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
Nicole545 (20141116)

20141116, 10:37 #4
 Join Date
 Oct 2014
 Posts
 21
 Thanks
 16
 Thanked 1 Time in 1 Post
Thank you both for helping me to resolve my problem. The values in column C cam be either Xs or free text values but are never blank. What makes it difficult is that even though the ranges will expand over time, I am not using the entire range. At any point, I may need to refer to only a portion based on the value of B1. The Countif/Choose is a nice solution and will be easy to update.
Hat's off to Maud and RG! Thank you so much!
Nicole