Table: create array of rows containing values

[tdcockers]

Hi all... I'm trying to build a crop plan (which crop is in which field, on a month to month basis) and then use that for calculating total area planted with different crops at any time.

Table looks like this:

I've been unable to figure out a formula that will essentially create an index of whether a value appears anywhere in a row that I can use to multiply by the area. For instance, if the lookup value was 'Chickpea 24/25' then the array would be {0, 1, 1, 0, 0, 0, 0, 0, 0, 1} that could then be multiplied with the Area column, to give a total area of 765. Any tips?

Thanks

 

[Georgiboy]

Perhaps the below will help, I have made a few assumptions:

Book1
	A	B	C	D	E	F	G	H	I
1	Field Plan	Area	Dec-23	Jan-24	Feb-24				Lookup List
2	G1	192	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24				BARLEY 23/24
3	G2	340	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25				CHICKPEA 24/25
4	G5	240	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25				Dave
5	L1	74	Dave	BARLEY 23/24	BARLEY 23/24
6	L2	254	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
7	L3	162	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
8	R1	343	BARLEY 23/24	Dave	BARLEY 23/24
9	R2	330	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
10	R3	125	BARLEY 23/24	BARLEY 23/24	Dave
11	R4	185	CHICKPEA 24/25	Dave	CHICKPEA 24/25
12	Total	2245
13
14	CHICKPEA 24/25		765	580	765
15
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=UNIQUE(TOCOL(Table1[[Dec-23]:[Feb-24]]))
B12	B12	=SUBTOTAL(109,[Area])
C14	C14	=SUMIF(Table1[Dec-23],$A$14,INDIRECT("Table1[Area]"))
D14	D14	=SUMIF(Table1[Jan-24],$A$14,INDIRECT("Table1[Area]"))
E14	E14	=SUMIF(Table1[Feb-24],$A$14,INDIRECT("Table1[Area]"))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A14	List	=I2#

 

[tdcockers]

Thanks. Not quite the outcome I was looking for, let me try to explain better. I'm possibly just making it unnecessarily difficult, but that's how we learn, right?

There is already a lookup list of all possible crops, which is also used as validation for the cells in the crop plan so values will always be exact matches. What I'm trying to do is write a formula that detemines if a field is used at any time by each crop in the plan, and put that in a column next to the lookup. So in your spreadsheet, cells J2:J4 would have 1480, 765 and 727 in them.

It's simple enough to do with a helper table/cells, but I'm trying to do it with a formula that is robust enough to deal with changes in the size of the crop plan both horizontally (adding future years while keeping historical ones) and vertically (adding fields).

 

[Georgiboy]

Are you looking to sum only the last column in the table, as below:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Field Plan	Area	Dec-23	Jan-24	Feb-24	Mar-24			Crop	Area
2	G1	192	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24			BARLEY 23/24	1480
3	G2	340	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25			CHICKPEA 24/25	765
4	G5	240	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25
5	L1	74	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
6	L2	254	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
7	L3	162	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
8	R1	343	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
9	R2	330	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
10	R3	125	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
11	R4	185	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=SUMIF(TAKE(Table1,,-1),I2:I3,Table1[Area])
Dynamic array formulas.

 

[Herakles]

Are you looking to sum only the last column in the table, as below:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Field Plan	Area	Dec-23	Jan-24	Feb-24	Mar-24			Crop	Area
2	G1	192	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24			BARLEY 23/24	1480
3	G2	340	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25			CHICKPEA 24/25	765
4	G5	240	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25
5	L1	74	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
6	L2	254	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
7	L3	162	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
8	R1	343	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
9	R2	330	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
10	R3	125	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24	BARLEY 23/24
11	R4	185	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25	CHICKPEA 24/25
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=SUMIF(TAKE(Table1,,-1),I2:I3,Table1[Area])
Dynamic array formulas.

It would be much easier to carry out any analyis if the data was structured so that each row represented the use in a field for a particular month.
This is a standard way of organisng such data.

This structure would then easily facilitate the adding of months, fields and crops.

The date column holds the first day of the month.

If put in a table, the formula would not need to be changed if another month is added.

Are you able to do this?

create array of rows containing values.xlsm
	A	B	C	D
1	Field Plan	Area	Date	Crop
2	G1	192	Dec 23	BARLEY 23/24
3	G2	340	Dec 23	CHICKPEA 24/25
4	G5	240	Dec 23	CHICKPEA 24/25
5	L1	74	Dec 23	Dave
6	L2	254	Dec 23	BARLEY 23/24
7	L3	162	Dec 23	BARLEY 23/24
8	R1	343	Dec 23	BARLEY 23/24
9	R2	330	Dec 23	BARLEY 23/24
10	R3	125	Dec 23	BARLEY 23/24
11	R4	185	Dec 23	CHICKPEA 24/25
12	G1	192	Jan 24	BARLEY 23/24
13	G2	340	Jan 24	CHICKPEA 24/25
14	G5	240	Jan 24	CHICKPEA 24/25
15	L1	74	Jan 24	BARLEY 23/24
16	L2	254	Jan 24	BARLEY 23/24
17	L3	162	Jan 24	BARLEY 23/24
18	R1	343	Jan 24	Dave
19	R2	330	Jan 24	BARLEY 23/24
20	R3	125	Jan 24	BARLEY 23/24
21	R4	185	Jan 24	Dave
22	G1	192	Feb 24	BARLEY 23/24
23	G2	340	Feb 24	CHICKPEA 24/25
24	G5	240	Feb 24	CHICKPEA 24/25
25	L1	74	Feb 24	BARLEY 23/24
26	L2	254	Feb 24	BARLEY 23/24
27	L3	162	Feb 24	BARLEY 23/24
28	R1	343	Feb 24	BARLEY 23/24
29	R2	330	Feb 24	BARLEY 23/24
30	R3	125	Feb 24	Dave
31	R4	185	Feb 24	CHICKPEA 24/25
Sheet1

 

[tdcockers]

Are you looking to sum only the last column in the table, as below:

Not quite, I'm trying to sum the area column when the crop appears anywhere in the table to the right of it. As Herakles suggested, an unpivoted version of the table would be simpler for analysis; but for a visual of what is in the ground when, and when it changes, the table is a much easier to parse view.

Having said all that, I that between you I have my solution... unpivot into dynamic array, then calculate areas. Thanks!

 

[Georgiboy]

Perhaps you could create the answers manually and show us what you want it to look like?

 

[tdcockers]

Thanks, I've solved this unpivoting the crosstab, then runnning the calculations against that. Always happy to hear improvements/suggestions of course. Columns A through E will be hidden in the working version.

20230927_FY23 Budget Prelim v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3						Crops				Field Plan		Jun 2023
4					Lookup	Name	Code	Area		Field	Area	Jul 2023	Aug 2023	Sep 2023	Oct 2023
5	G1	45108	BARLEY 23/24		BARLEY 23/24	BARLEY 23/24	10	686		G1	192.00	BARLEY 23/24	BARLEY 23/24	WHEAT 23/24	WHEAT 23/24
6	G1	45139	BARLEY 23/24		BARLEY 24/25	BARLEY 24/25	11	0		G2	340.00	FALLOW	SORGHUM 23/24	SORGHUM 23/24	CHICKPEA 23/24
7	G1	45170	WHEAT 23/24		WHEAT 23/24	WHEAT 23/24	20	446		G5	240.00	FALLOW	BARLEY 23/24	SORGHUM 23/24	SORGHUM 23/24
8	G1	45200	WHEAT 23/24		WHEAT 24/25	WHEAT 24/25	21	0		L1	74.00	CHICKPEA 23/24	CHICKPEA 23/24	CHICKPEA 23/24	CHICKPEA 23/24
9	G2	45108	FALLOW		SORGHUM 23/24	SORGHUM 23/24	30	580		L2	254.00	FALLOW	WHEAT 23/24	WHEAT 23/24	BARLEY 23/24
10	G2	45139	SORGHUM 23/24		SORGHUM 24/25	SORGHUM 24/25	31	0		L3	162.00
11	G2	45170	SORGHUM 23/24		CHICKPEA 23/24	CHICKPEA 23/24	40	414		R1	343.00
12	G2	45200	CHICKPEA 23/24		CHICKPEA 24/25	CHICKPEA 24/25	41	0		R2	330.00
13	G5	45108	FALLOW		FALLOW	FALLOW	90	834		R3	125.00
14	G5	45139	BARLEY 23/24		GENERAL/OVERHEAD	GENERAL/OVERHEAD	99	0		R4	185.00
15	G5	45170	SORGHUM 23/24							Total	2,245.00
16	G5	45200	SORGHUM 23/24
17	L1	45108	CHICKPEA 23/24
18	L1	45139	CHICKPEA 23/24							Rotation Summary
19	L1	45170	CHICKPEA 23/24							Field	1	2	3	4	5
20	L1	45200	CHICKPEA 23/24							G1	BARLEY 23/24	WHEAT 23/24
21	L2	45108	FALLOW							G2	FALLOW	SORGHUM 23/24	CHICKPEA 23/24
22	L2	45139	WHEAT 23/24							G5	FALLOW	BARLEY 23/24	SORGHUM 23/24
23	L2	45170	WHEAT 23/24							L1	CHICKPEA 23/24
24	L2	45200	BARLEY 23/24							L2	FALLOW	WHEAT 23/24	BARLEY 23/24
25	L3	45108								L3
26	L3	45139								R1
27	L3	45170								R2
28	L3	45200								R3
29	R1	45108								R4
Field Plan

Cell Formulas
Range	Formula
L4:O4	L4	=EDATE(L3,SEQUENCE(1,COLUMNS(tblFields)-2))
A5:C44	A5	=TEXTSPLIT(TEXTJOIN("^",FALSE,TOCOL(tblFields[Field Plan]&"|"&L4#&"|"&DROP(tblFields,0,2))),"|","^")
E5:E14	E5	=tblCrops[Name]
H5:H14	H5	=SUM(SUMIF(tblFields[Field Plan],UNIQUE(CHOOSECOLS(FILTER($A$5#,CHOOSECOLS($A$5#,3)=[@Name]),1)),tblFields[Area]))
K15	K15	=SUBTOTAL(109,[Area])
K19:O19	K19	=SEQUENCE(1,COLUMNS(tblRotations)-1)
K20:O29	K20	=IFERROR(INDEX(TRANSPOSE(UNIQUE(CHOOSECOLS(FILTER($A$5#,CHOOSECOLS($A$5#,1)=[@Column1]),3))),1,K$19),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F:DW	Expression	=SEARCH("FALLOW",F1)=1	text	NO
F:DW	Expression	=SEARCH("BARLEY",F1)=1	text	NO
F:DW	Expression	=SEARCH("WHEAT",F1)=1	text	NO
F:DW	Expression	=SEARCH("SORGHUM",F1)=1	text	NO
F:DW	Expression	=SEARCH("CHICKPEA",F1)=1	text	NO
4:4	Expression	=A4<>""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L5:O14	List	=$E$5#