AGGREGATE Function With Errors

[excelbytes]

I have this formula:

=AGGREGATE(9,6,LEFT(OFFSET('Form Responses'!$K$1,1,MATCH('ORDER QTY TOTALS'!B63,'Form Responses'!$L$1:$DI$1,0),COUNTA('Form Responses'!A:A),1),1)*12)

The array of the formula resolves to:

{12;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

I'm trying to convert the number of dozens to units (e.g. 5 dozen = 60)

I'm not sure why this isn't working.

The range that is returned by the OFFSET function is:

1 DOZEN

2 DOZEN

2 DOZEN

 

[Scott Huish]

I was hoping someone would have an answer to this. In my experience AGGREGATE seems to ignore errors if it's already in the source, but if the errors are in an array created within the AGGREGATE function like your example, it doesn't seem to ignore them. I ran into a similar situation the other day.

 

[Fluff]

The aggregate function only works with an array if the 1st argument is 14 or higher. With 13 or lower you need to use a range.
Can you post some sample data using the XL2BB add-in

 

[excelbytes]

The aggregate function only works with an array if the 1st argument is 14 or higher. With 13 or lower you need to use a range.
Can you post some sample data using the XL2BB add-in

AGGREGATE - Dozen Conversion.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Order Date	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10					Correct Responses
2	10-21-2024 10:55:46	1 DOZEN	1 DOZEN	1 DOZEN	1 DOZEN	1 DOZEN	1 DOZEN	1 DOZEN	1 DOZEN	1 DOZEN	1 DOZEN		Item 1	#VALUE!		60
3	10-21-2024 12:22:54												Item 2	#VALUE!		12
4	10-21-2024 15:45:51	2 DOZEN							1 DOZEN				Item 3	#VALUE!		24
5	10-21-2024 18:39:21												Item 4	#VALUE!		24
6	10-21-2024 18:49:07												Item 5	#VALUE!		24
7	10-21-2024 18:58:24												Item 6	#VALUE!		24
8	10-21-2024 19:26:15												Item 7	#VALUE!		24
9	10-21-2024 19:30:15												Item 8	#VALUE!		60
10	10-21-2024 19:40:03												Item 9	#VALUE!		12
11	10-21-2024 20:32:35												Item 10	#VALUE!		12
12	10-21-2024 20:42:20						1 DOZEN		1 DOZEN
13	10-21-2024 20:48:17
14	10-21-2024 21:08:02
15	10-21-2024 21:24:49				1 DOZEN	1 DOZEN
16	10-21-2024 21:35:43
17	10-21-2024 23:01:48			1 DOZEN				1 DOZEN	1 DOZEN
18	10-22-2024 0:34:17	2 DOZEN							1 DOZEN
19	10-22-2024 5:04:49
20	10-22-2024 6:29:35
21	10-22-2024 7:06:52
22	10-22-2024 7:28:55
Sheet1

Cell Formulas
Range	Formula
N2:N11	N2	=AGGREGATE(9,6,LEFT(OFFSET($A$1,1,MATCH(M2,$B$1:$K$1,0),COUNTA(A:A),1),1)*12)

 

[Fluff]

Thanks for that, how about

=SUM(FILTER(LEFT($B$2:$K$1000&"0"),$B$1:$K$1=M2)*12)

 

[excelbytes]

Thanks for that, how about

=SUM(FILTER(LEFT($B$2:$K$1000&"0"),$B$1:$K$1=M2)*12)

That works great! Can you explain how the "LEFT($B$2:$K$1000&"0")" component works?

 

[Fluff]

It adds a 0 to the end of every cell, so that the empty cells have a 0 in them & the Left function returns the 1st character.

 

[excelbytes]

It adds a 0 to the end of every cell, so that the empty cells have a 0 in them & the Left function returns the 1st character.

Got it! Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

Another option (still assuming all values < 10 dozen) would be

=SUM(TOCOL((B$1:K$1=M2)*LEFT(B$2:K$22)*12,3))

.. or if you wanted all the results without copying the formula down the column

=BYROW(M2:M11,LAMBDA(r,SUM(TOCOL((B1:K1=r)*LEFT(B2:K22)*12,3))))