GROUPBY error in the array filter

[Fxingenieria]

I was triying to do this example from MrExcel, but I get a #VALUE! error. Please can you review in the file I am sending, the red sheet, I painted with yellow the formula. Thank you so much!!

 

[Dave Patton]

You are not providing very much information and an image doesn't help.

Is Groupby function available on your system?
Move to an empty cell.
Start to enter the function =Group does =Groupby( show?

Have you used Groupby or PivotBy with other examples?

 

[Fxingenieria]

You are not providing very much information and an image doesn't help.

Is Groupby function available on your system?
Move to an empty cell.
Start to enter the function =Group does =Groupby( show?

Have you used Groupby or PivotBy with other examples?

Thanks for your answer. The Groupby function is availabre in my system, 365. The error comes from the filter_array part of the function, this part goes to #VALUE! error.

 

[Fxingenieria]

Thanks for your answer. The Groupby function is availabre in my system, 365. The error comes from the filter_array part of the function, this part goes to #VALUE! error.

I have used Groupby in other examples and I get the same error of this part of function, the filter_array.

 

[Dave Patton]

If this is the same data, the formula works.
I noticed that you are using semicolons; you could try the formula with commas.

GroupBy_PivotBy_PercentOf_Functions_2633.xlsx
	A	B	C	D	E	F	G	H	I
1								GroupBy
2
3
4	Category	Product	Sales	Cost
5	Fruit	Apple	1,275	689
6	Vegetables	Asparagus	4,864	2,335		Category	Product	Sales	Cost
7	Fruit	Banana	3,809	1,828		Fruit	Cherry	9,936	5,365
8	Herbs	Basil	8,088	3,640		Fruit	Dill	9,855	5,223
9	Vegetables	Broccoli	3,943	2,090		Fruit	Fig	9,028	4,965
10	Vegetables	Cabbage	2,653	1,353		Fruit	Guava	5,385	2,854
11	Fruit	Cherry	9,936	5,365		Fruit	Banana	3,809	1,828
12	Herbs	Cilantro	3,967	2,142		Fruit	Elderberry	2,768	1,439
13	Fruit	Dill	9,855	5,223		Fruit	Apple	1,275	689
14	Fruit	Elderberry	2,768	1,439		Fruit		42,056	22,363
15	Vegetables	Endive	7,164	3,725		Vegetables	Peppers	8,556	3,936
16	Herbs	Fennel	9,514	4,376		Vegetables	Endive	7,164	3,725
17	Fruit	Fig	9,028	4,965		Vegetables	Asparagus	4,864	2,335
18	Fruit	Guava	5,385	2,854		Vegetables	Broccoli	3,943	2,090
19	Herbs	Oregano	7,262	3,341		Vegetables	Cabbage	2,653	1,353
20	Herbs	Parsley	3,540	1,628		Vegetables		27,180	13,439
21	Vegetables	Peppers	8,556	3,936		Grand Total		69,236	35,802
22
Groupby

Cell Formulas
Range	Formula
F6:I21	F6	=GROUPBY(A4:B21,C4:D21,SUM,3,2,-3,A4:A21<>"Herbs")
Dynamic array formulas.

 

[Fxingenieria]

If this is the same data, the formula works.
I noticed that you are using semicolons; you could try the formula with commas.

GroupBy_PivotBy_PercentOf_Functions_2633.xlsx
	A	B	C	D	E	F	G	H	I
1								GroupBy
2
3
4	Category	Product	Sales	Cost
5	Fruit	Apple	1,275	689
6	Vegetables	Asparagus	4,864	2,335		Category	Product	Sales	Cost
7	Fruit	Banana	3,809	1,828		Fruit	Cherry	9,936	5,365
8	Herbs	Basil	8,088	3,640		Fruit	Dill	9,855	5,223
9	Vegetables	Broccoli	3,943	2,090		Fruit	Fig	9,028	4,965
10	Vegetables	Cabbage	2,653	1,353		Fruit	Guava	5,385	2,854
11	Fruit	Cherry	9,936	5,365		Fruit	Banana	3,809	1,828
12	Herbs	Cilantro	3,967	2,142		Fruit	Elderberry	2,768	1,439
13	Fruit	Dill	9,855	5,223		Fruit	Apple	1,275	689
14	Fruit	Elderberry	2,768	1,439		Fruit		42,056	22,363
15	Vegetables	Endive	7,164	3,725		Vegetables	Peppers	8,556	3,936
16	Herbs	Fennel	9,514	4,376		Vegetables	Endive	7,164	3,725
17	Fruit	Fig	9,028	4,965		Vegetables	Asparagus	4,864	2,335
18	Fruit	Guava	5,385	2,854		Vegetables	Broccoli	3,943	2,090
19	Herbs	Oregano	7,262	3,341		Vegetables	Cabbage	2,653	1,353
20	Herbs	Parsley	3,540	1,628		Vegetables		27,180	13,439
21	Vegetables	Peppers	8,556	3,936		Grand Total		69,236	35,802
22
Groupby

Cell Formulas
Range	Formula
F6:I21	F6	=GROUPBY(A4:B21,C4:D21,SUM,3,2,-3,A4:A21<>"Herbs")
Dynamic array formulas.

If this is the same data, the formula works.
I noticed that you are using semicolons; you could try the formula with commas.

GroupBy_PivotBy_PercentOf_Functions_2633.xlsx
	A	B	C	D	E	F	G	H	I
1								GroupBy
2
3
4	Category	Product	Sales	Cost
5	Fruit	Apple	1,275	689
6	Vegetables	Asparagus	4,864	2,335		Category	Product	Sales	Cost
7	Fruit	Banana	3,809	1,828		Fruit	Cherry	9,936	5,365
8	Herbs	Basil	8,088	3,640		Fruit	Dill	9,855	5,223
9	Vegetables	Broccoli	3,943	2,090		Fruit	Fig	9,028	4,965
10	Vegetables	Cabbage	2,653	1,353		Fruit	Guava	5,385	2,854
11	Fruit	Cherry	9,936	5,365		Fruit	Banana	3,809	1,828
12	Herbs	Cilantro	3,967	2,142		Fruit	Elderberry	2,768	1,439
13	Fruit	Dill	9,855	5,223		Fruit	Apple	1,275	689
14	Fruit	Elderberry	2,768	1,439		Fruit		42,056	22,363
15	Vegetables	Endive	7,164	3,725		Vegetables	Peppers	8,556	3,936
16	Herbs	Fennel	9,514	4,376		Vegetables	Endive	7,164	3,725
17	Fruit	Fig	9,028	4,965		Vegetables	Asparagus	4,864	2,335
18	Fruit	Guava	5,385	2,854		Vegetables	Broccoli	3,943	2,090
19	Herbs	Oregano	7,262	3,341		Vegetables	Cabbage	2,653	1,353
20	Herbs	Parsley	3,540	1,628		Vegetables		27,180	13,439
21	Vegetables	Peppers	8,556	3,936		Grand Total		69,236	35,802
22
Groupby

Cell Formulas
Range	Formula
F6:I21	F6	=GROUPBY(A4:B21,C4:D21,SUM,3,2,-3,A4:A21<>"Herbs")
Dynamic array formulas.

Thanks, I will change to comma to see if it works.

 

[Fxingenieria]

Thanks, I will change to comma to see if it works.

I changed to commas but the error is still there. Dropbox

 

[Dave Patton]

See post #5
Copy the example to a clean sheet.
N.B. Click on the icon below the f(x) in the header, move to your sheet's A1, and paste.

Does your system provide the same results?

 

[Dave Patton]

I tried editing the formula and it not longer works.
You are correct; the formula fails with the filter part. It yields a value error.

 

[Dave Patton]

I tried many edits and workarounds but none of them work.

I sent a feedback post to Microsoft with the inquiry.