Need help condensing an IF Statement

[ceytl]

Is there an easier way to write this formula?

=IF(A1="US",(B1<>"")*100,(B1<>"")*150)+IF(A1="US",(C1<>"")*225,(C1<>"")*275)+IF(A1="US",(D1<>"")*50,(D1<>"")*90)+IF(A1="US",(E1<>"")*400,(E1<>"")*425)

Also, instead of using <>"", I would like it to only show the value if there is 9 digits inside the reference cell, like: 522789333, but I'm not sure if that is possible?

Thanks!

 

[Peter_SSs]

Could you give us 5-6 rows of sample data and expected results with XL2BB? That might clear up things like whether columns B:E get filled from the left or whether they can contain values randomly or whether there is only 1 value in those columns in each row, whether the data is numerical or text etc. You are very familiar with your data, but we have no idea.

Could you also update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ceytl]

Could you give us 5-6 rows of sample data and expected results with XL2BB? That might clear up things like whether columns B:E get filled from the left or whether they can contain values randomly or whether there is only 1 value in those columns in each row, whether the data is numerical or text etc. You are very familiar with your data, but we have no idea.

Could you also update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

View attachment 15135

Here's the data:

Test.xlsx
	H
25
Sheet1

Sorry, my spreadsheet is more detailed than my original post.

I am trying to see if there's an easier way to write the formula, so I don't have 12 IF Statements.

Instead of using <>"" in my reference cells, is there a way to have a cell return the value with only 9 digits in it?

Currently all cells are blank or only have 9 numbers in them.

Thanks for your help!

 

[ceytl]

Sorry, I didn't post the data correctly:

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Bracket	BG 100	BG 200	BG 300	MG 100	MG 200	FG 100	HG 100	HG 200	HG 300	HTG 100	HTG 200	RMG 500	Total		Bracket 1 - 3	Bracket 4 - 6
2	1 to 3		440149018		440149018		440862815	440614014			440614021			$1,050.00	BG 100	$ 100.00	$ 200.00
3	4 to 6	440840416							440683129				440840399	$ 800.00	BG 200	$ 150.00	$ 350.00
4	4 to 6		440840423	440614020	440840421	440862889		440614013		440613993				$2,000.00	BG 300	$ 200.00	$ 400.00
5	1 to 3	440840413				440700254	440614005				440700254			$1,000.00	MG 100	$ 250.00	$ 300.00
6	4 to 6				440840428				439468577					$ 550.00	MG 200	$ 350.00	$ 400.00
7	1 to 3		440840358									440840431		$ 550.00	FG 100	$ 50.00	$ 100.00
8	4 to 6			440614022				440683230						$ 550.00	HG 100	$ 100.00	$ 150.00
9	1 to 3							440614009					440840397	$ 400.00	HG 200	$ 200.00	$ 250.00
10	1 to 3	440840360								914154596				$ 400.00	HG 300	$ 300.00	$ 400.00
11	1 to 3								439468577					$ 200.00	HTG 100	$ 500.00	$ 600.00
12	4 to 6						440862820				440840431			$ 700.00	HTG 200	$ 400.00	$ 450.00
13	4 to 6		981233745			440140351								$ 750.00	RMG 500	$ 300.00	$ 350.00
Sheet1

Cell Formulas
Range	Formula
N2:N13	N2	=IF($A2="1 to 3",($B2<>"")*$P$2,($B2<>"")*$Q$2)+IF($A2="1 to 3",($C2<>"")*$P$3,($C2<>"")*$Q$3)+IF($A2="1 to 3",($D2<>"")*$P$4,($D2<>"")*$Q$4)+IF($A2="1 to 3",($E2<>"")*$P$5,($E2<>"")*$Q$5)+IF($A2="1 to 3",($F2<>"")*$P$6,($F2<>"")*$Q$6)+IF($A2="1 to 3",($G2<>"")*$P$7,($G2<>"")*$Q$7)+IF($A2="1 to 3",($H2<>"")*$P$8,($H2<>"")*$Q$8)+IF($A2="1 to 3",($I2<>"")*$P$9,($I2<>"")*$Q$9)+IF($A2="1 to 3",($J2<>"")*$P$10,($J2<>"")*$Q$10)+IF($A2="1 to 3",($K2<>"")*$P$11,($K2<>"")*$Q$11)+IF($A2="1 to 3",($L2<>"")*$P$12,($L2<>"")*$Q$12)+IF($A2="1 to 3",($M2<>"")*$P$13,($M2<>"")*$Q$13)

 

[Peter_SSs]

Sorry, my spreadsheet is more detailed than my original post.

Which is why we needed to know about it.

See if this, copied down, does what you want.

20 05 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Bracket	BG 100	BG 200	BG 300	MG 100	MG 200	FG 100	HG 100	HG 200	HG 300	HTG 100	HTG 200	RMG 500	Total		Bracket 1 - 3	Bracket 4 - 6
2	1 to 3		440149018		440149018		440862815	440614014			440614021			1050	BG 100	100	200
3	4 to 6	440840416							440683129				440840399	800	BG 200	150	350
4	4 to 6		440840423	440614020	440840421	440862889		440614013		440613993				2000	BG 300	200	400
5	1 to 3	440840413				440700254	440614005				440700254			1000	MG 100	250	300
6	4 to 6				440840428				439468577					550	MG 200	350	400
7	1 to 3		440840358									440840431		550	FG 100	50	100
8	4 to 6			440614022				440683230						550	HG 100	100	150
9	1 to 3							440614009					440840397	400	HG 200	200	250
10	1 to 3	440840360								914154596				400	HG 300	300	400
11	1 to 3								439468577					200	HTG 100	500	600
12	4 to 6						440862820				440840431			700	HTG 200	400	450
13	4 to 6		981233745			440140351								750	RMG 500	300	350
ceytl

Cell Formulas
Range	Formula
N2:N13	N2	=SUMPRODUCT(IF(A2="1 to 3",P$2:P$13,Q$2:Q$13)*TRANSPOSE(B2:M2<>""))

 

[ceytl]

Which is why we needed to know about it.

See if this, copied down, does what you want.

20 05 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Bracket	BG 100	BG 200	BG 300	MG 100	MG 200	FG 100	HG 100	HG 200	HG 300	HTG 100	HTG 200	RMG 500	Total		Bracket 1 - 3	Bracket 4 - 6
2	1 to 3		440149018		440149018		440862815	440614014			440614021			1050	BG 100	100	200
3	4 to 6	440840416							440683129				440840399	800	BG 200	150	350
4	4 to 6		440840423	440614020	440840421	440862889		440614013		440613993				2000	BG 300	200	400
5	1 to 3	440840413				440700254	440614005				440700254			1000	MG 100	250	300
6	4 to 6				440840428				439468577					550	MG 200	350	400
7	1 to 3		440840358									440840431		550	FG 100	50	100
8	4 to 6			440614022				440683230						550	HG 100	100	150
9	1 to 3							440614009					440840397	400	HG 200	200	250
10	1 to 3	440840360								914154596				400	HG 300	300	400
11	1 to 3								439468577					200	HTG 100	500	600
12	4 to 6						440862820				440840431			700	HTG 200	400	450
13	4 to 6		981233745			440140351								750	RMG 500	300	350
ceytl

Cell Formulas
Range	Formula
N2:N13	N2	=SUMPRODUCT(IF(A2="1 to 3",P$2:P$13,Q$2:Q$13)*TRANSPOSE(B2:M2<>""))

Thanks, it works great!

I have one more question, if I add a date column on every other column, how would you write the code so it doesn't add up the date column, but everything else?

Test.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	BG 200	Date	BG 300	Date	MG 100	Date	MG 200	Date	FG 100	Date	HG 100	Date	HG 200	Date	HG 300	Date	HTG 100	Date	HTG 200	Date	RMG 500	Total		Bracket 1 - 3	Bracket 4 - 6
2	440149018	5/20/2020		5/20/2020	440149018	5/20/2020		5/20/2020	440862815	5/20/2020	440614014	5/20/2020		5/20/2020		5/20/2020	440614021	5/20/2020		5/20/2020		#N/A	BG 100	$ 100.00	$ 200.00
3		5/21/2020		5/21/2020		5/21/2020		5/21/2020		5/21/2020		5/21/2020	440683129	5/21/2020		5/21/2020		5/21/2020		5/21/2020	440840399	#N/A	BG 200	$ 150.00	$ 350.00
4	440840423	5/22/2020	440614020	5/22/2020	440840421	5/22/2020	440862889	5/22/2020		5/22/2020	440614013	5/22/2020		5/22/2020	440613993	5/22/2020		5/22/2020		5/22/2020		#N/A	BG 300	$ 200.00	$ 400.00
5		5/23/2020		5/23/2020		5/23/2020	440700254	5/23/2020	440614005	5/23/2020		5/23/2020		5/23/2020		5/23/2020	440700254	5/23/2020		5/23/2020		#N/A	MG 100	$ 250.00	$ 300.00
6		5/24/2020		5/24/2020	440840428	5/24/2020		5/24/2020		5/24/2020		5/24/2020	439468577	5/24/2020		5/24/2020		5/24/2020		5/24/2020		#N/A	MG 200	$ 350.00	$ 400.00
7	440840358	5/25/2020		5/25/2020		5/25/2020		5/25/2020		5/25/2020		5/25/2020		5/25/2020		5/25/2020		5/25/2020	440840431	5/25/2020		#N/A	FG 100	$ 50.00	$ 100.00
8		5/26/2020	440614022	5/26/2020		5/26/2020		5/26/2020		5/26/2020	440683230	5/26/2020		5/26/2020		5/26/2020		5/26/2020		5/26/2020		#N/A	HG 100	$ 100.00	$ 150.00
9		5/27/2020		5/27/2020		5/27/2020		5/27/2020		5/27/2020	440614009	5/27/2020		5/27/2020		5/27/2020		5/27/2020		5/27/2020	440840397	#N/A	HG 200	$ 200.00	$ 250.00
10		5/28/2020		5/28/2020		5/28/2020		5/28/2020		5/28/2020		5/28/2020		5/28/2020	914154596	5/28/2020		5/28/2020		5/28/2020		#N/A	HG 300	$ 300.00	$ 400.00
11		5/29/2020		5/29/2020		5/29/2020		5/29/2020		5/29/2020		5/29/2020	439468577	5/29/2020		5/29/2020		5/29/2020		5/29/2020		#N/A	HTG 100	$ 500.00	$ 600.00
12		5/30/2020		5/30/2020		5/30/2020		5/30/2020	440862820	5/30/2020		5/30/2020		5/30/2020		5/30/2020	440840431	5/30/2020		5/30/2020		#N/A	HTG 200	$ 400.00	$ 450.00
13	981233745	5/31/2020		5/31/2020		5/31/2020	440140351	5/31/2020		5/31/2020		5/31/2020		5/31/2020		5/31/2020		5/31/2020		5/31/2020		#N/A	RMG 500	$ 300.00	$ 350.00
Sheet1

Cell Formulas
Range	Formula
Y2:Y13	Y2	=SUMPRODUCT(IF(A2="1 to 3",AA$2:AA$13,AB$2:AB$13)*TRANSPOSE(B2:X2<>""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

if I add a date column on every other column, how would you write the code so it doesn't add up the date column, but everything else?

Try this

20 05 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Bracket	BG 100	Date	BG 200	Date	BG 300	Date	MG 100	Date	MG 200	Date	FG 100	Date	HG 100	Date	HG 200	Date	HG 300	Date	HTG 100	Date	HTG 200	Date	RMG 500	Date	Total		Bracket 1 - 3	Bracket 4 - 6
2	1 to 3		5/05/2020	440149018	5/05/2020		5/05/2020	440149018	5/05/2020		5/05/2020	440862815	5/05/2020	440614014	5/05/2020		5/05/2020		5/05/2020	440614021	5/05/2020		5/05/2020		5/05/2020	1050	BG 100	100	200
3	4 to 6	440840416	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440683129	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840399	5/05/2020	800	BG 200	150	350
4	4 to 6		5/05/2020	440840423	5/05/2020	440614020	5/05/2020	440840421	5/05/2020	440862889	5/05/2020		5/05/2020	440614013	5/05/2020		5/05/2020	440613993	5/05/2020		5/05/2020		5/05/2020		5/05/2020	2000	BG 300	200	400
5	1 to 3	440840413	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440700254	5/05/2020	440614005	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440700254	5/05/2020		5/05/2020		5/05/2020	1000	MG 100	250	300
6	4 to 6		5/05/2020		5/05/2020		5/05/2020	440840428	5/05/2020		5/05/2020		5/05/2020		5/05/2020	439468577	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	550	MG 200	350	400
7	1 to 3		5/05/2020	440840358	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840431	5/05/2020		5/05/2020	550	FG 100	50	100
8	4 to 6		5/05/2020		5/05/2020	440614022	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440683230	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	550	HG 100	100	150
9	1 to 3		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440614009	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840397	5/05/2020	400	HG 200	200	250
10	1 to 3	440840360	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	914154596	5/05/2020		5/05/2020		5/05/2020		5/05/2020	400	HG 300	300	400
11	1 to 3		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	439468577	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	200	HTG 100	500	600
12	4 to 6		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440862820	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840431	5/05/2020		5/05/2020		5/05/2020	700	HTG 200	400	450
13	4 to 6		5/05/2020	981233745	5/05/2020		5/05/2020		5/05/2020	440140351	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	750	RMG 500	300	350
ceytl (2)

Cell Formulas
Range	Formula
Z2:Z13	Z2	=SUMPRODUCT((B2:X2<>"")*IFERROR(VLOOKUP(B$1:X$1,AA$2:AC$13,2+(A2="4 to 6"),0),0))

 

[ceytl]

Try this

20 05 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Bracket	BG 100	Date	BG 200	Date	BG 300	Date	MG 100	Date	MG 200	Date	FG 100	Date	HG 100	Date	HG 200	Date	HG 300	Date	HTG 100	Date	HTG 200	Date	RMG 500	Date	Total		Bracket 1 - 3	Bracket 4 - 6
2	1 to 3		5/05/2020	440149018	5/05/2020		5/05/2020	440149018	5/05/2020		5/05/2020	440862815	5/05/2020	440614014	5/05/2020		5/05/2020		5/05/2020	440614021	5/05/2020		5/05/2020		5/05/2020	1050	BG 100	100	200
3	4 to 6	440840416	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440683129	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840399	5/05/2020	800	BG 200	150	350
4	4 to 6		5/05/2020	440840423	5/05/2020	440614020	5/05/2020	440840421	5/05/2020	440862889	5/05/2020		5/05/2020	440614013	5/05/2020		5/05/2020	440613993	5/05/2020		5/05/2020		5/05/2020		5/05/2020	2000	BG 300	200	400
5	1 to 3	440840413	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440700254	5/05/2020	440614005	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440700254	5/05/2020		5/05/2020		5/05/2020	1000	MG 100	250	300
6	4 to 6		5/05/2020		5/05/2020		5/05/2020	440840428	5/05/2020		5/05/2020		5/05/2020		5/05/2020	439468577	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	550	MG 200	350	400
7	1 to 3		5/05/2020	440840358	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840431	5/05/2020		5/05/2020	550	FG 100	50	100
8	4 to 6		5/05/2020		5/05/2020	440614022	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440683230	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	550	HG 100	100	150
9	1 to 3		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440614009	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840397	5/05/2020	400	HG 200	200	250
10	1 to 3	440840360	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	914154596	5/05/2020		5/05/2020		5/05/2020		5/05/2020	400	HG 300	300	400
11	1 to 3		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	439468577	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	200	HTG 100	500	600
12	4 to 6		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440862820	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840431	5/05/2020		5/05/2020		5/05/2020	700	HTG 200	400	450
13	4 to 6		5/05/2020	981233745	5/05/2020		5/05/2020		5/05/2020	440140351	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	750	RMG 500	300	350
ceytl (2)

Cell Formulas
Range	Formula
Z2:Z13	Z2	=SUMPRODUCT((B2:X2<>"")*IFERROR(VLOOKUP(B$1:X$1,AA$2:AC$13,2+(A2="4 to 6"),0),0))

Thanks for helping!

I am having a problem with the worksheet not totaling correctly. Row 2 should total $1,550, but it shows $1,050

I don't want the date columns to add, but it shows them adding as part of the total when I enter a date.

Thanks for all your help!

 

[Peter_SSs]

Row 2 should total $1,550, but it shows $1,050

I think I might have had the Date columns on the wrong side of the other columns, corrected below. However, I still get 1,050 for row 2. As I understand the problem the formula for row 2 should sum the coloured values in column AB, which it does. I used your sample data from post #4 where your original formula also gave 1050. How/why do you now calculate 1550 for that row?

20 05 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Bracket	Date	BG 100	Date	BG 200	Date	BG 300	Date	MG 100	Date	MG 200	Date	FG 100	Date	HG 100	Date	HG 200	Date	HG 300	Date	HTG 100	Date	HTG 200	Date	RMG 500	Total		Bracket 1 - 3	Bracket 4 - 6
2	1 to 3	5/05/2020		5/05/2020	440149018	5/05/2020		5/05/2020	440149018	5/05/2020		5/05/2020	440862815	5/05/2020	440614014	5/05/2020		5/05/2020		5/05/2020	440614021	5/05/2020		5/05/2020		1050	BG 100	100	200
3	4 to 6	5/05/2020	440840416	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440683129	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840399	800	BG 200	150	350
4	4 to 6	5/05/2020		5/05/2020	440840423	5/05/2020	440614020	5/05/2020	440840421	5/05/2020	440862889	5/05/2020		5/05/2020	440614013	5/05/2020		5/05/2020	440613993	5/05/2020		5/05/2020		5/05/2020		2000	BG 300	200	400
5	1 to 3	5/05/2020	440840413	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440700254	5/05/2020	440614005	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440700254	5/05/2020		5/05/2020		1000	MG 100	250	300
6	4 to 6	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840428	5/05/2020		5/05/2020		5/05/2020		5/05/2020	439468577	5/05/2020		5/05/2020		5/05/2020		5/05/2020		550	MG 200	350	400
7	1 to 3	5/05/2020		5/05/2020	440840358	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840431	5/05/2020		550	FG 100	50	100
8	4 to 6	5/05/2020		5/05/2020		5/05/2020	440614022	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440683230	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		550	HG 100	100	150
9	1 to 3	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440614009	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840397	400	HG 200	200	250
10	1 to 3	5/05/2020	440840360	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	914154596	5/05/2020		5/05/2020		5/05/2020		400	HG 300	300	400
11	1 to 3	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	439468577	5/05/2020		5/05/2020		5/05/2020		5/05/2020		200	HTG 100	500	600
12	4 to 6	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020	440862820	5/05/2020		5/05/2020		5/05/2020		5/05/2020	440840431	5/05/2020		5/05/2020		700	HTG 200	400	450
13	4 to 6	5/05/2020		5/05/2020	981233745	5/05/2020		5/05/2020		5/05/2020	440140351	5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		5/05/2020		750	RMG 500	300	350
ceytl (2)

Cell Formulas
Range	Formula
Z2:Z13	Z2	=SUMPRODUCT((B2:Y2<>"")*IFERROR(VLOOKUP(B$1:Y$1,AA$2:AC$13,2+(A2="4 to 6"),0),0))