Removing the need of a helper column

[Marvo]

Good morning, yesterday I posed a question regarding the attached image and Stephen Crump kindly gave me the perfect solution.

=COUNTIFS(A2:A34,"League",B2:B34,"H",D2:D34,E14)

What has got me thinking is the solution doesn't reference column C at all and as column D is a helper column that refers to Column C, is it possible to "cut out the middle man" completely, meaning I could dispense with the helper column? It would be a great boon if so as the several helper columns (60 in all) really slow the workbook down.

The formula in D3 is =IF(($A3="League")*($B3="H"),IF($C3="W",SUM(1,D2),0),N(D2))

Copied down

Many thanks for any help offered and if it can't be done, so be it. Just wondered.

 

[Marvo]

 

[KRice]

Unmerge any cells in column A and anywhere else in the summary table, then in the formula where you see
t, $A$20:$Q$35
edit the range to cover the full summary table…upper left where Consecutives is found to lower right, the last cell in the results for Longest Runs. After the formula is in place, you might be able to merge cells again. I’m just guessing, but that’s what I did. I normally avoid merged cells altogether.

 

[Marvo]

Okay, I'll give it a go.

 

[Marvo]

Done that, its now saying the formula is missing an opening or closing parenthesis.

=LET(loc, $B22, src, All!$B$2:$M$7000, t, $A$2:$Q$17, a, INDEX(t, , 1),
idxCon, MATCH("Consecutives", a, 0), idxLR, MATCH("Longest Runs", a, 0), curridx, ROW() - ROW(INDEX(a, 1)) + 1,
dyna, INDEX(a, SEQUENCE(curridx)),
mt, INDEX(dyna, XMATCH(TRUE, dyna <> "", 0, -1)),
ct, IF(curridx >= idxLR, "Longest Runs", "Consecutives"),
oc_input, IF(curridx >= idxLR, INDEX(t, idxLR, COLUMN()), INDEX(t, idxCon, COLUMN())),
oc, XLOOKUP(oc_input, {"W","NO L","D","NO D","L","NO W","GF","NO GA","GA","NO GF"}, {"W","L","D","D","L","W","S","S","S","S"}),
sect, IFS(OR(oc_input = {"W","D","L","NO L","NO D","NO W"}), 1, OR(oc_input = {"GF","NO GF"}), 2, OR(oc_input = {"GA","NO GA"}), 3),
fsrc_cols, CHOOSE(sect, {1,4,9,10}, {1,4,9,11}, {1,4,9,12}),
fsrc, FILTER(src, INDEX(src, , 10) <> ""),
data, INDEX(fsrc, SEQUENCE(ROWS(fsrc)), fsrc_cols),
allr_data, SEQUENCE(ROWS(data), , 1, 0),
loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc),
mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt),
fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1,4}), loc_ary * mt_ary),
allr_fd, SEQUENCE(ROWS(fd)),
oc_col, IFS(sect = 1, INDEX(fd, , 2), OR(sect = 2, sect = 3), IF(INDEX(fd, , 2) > 0, "S", "N")),
revfd, IF(SWITCH(ct, "Consecutives", oc_col = oc, "Longest Runs", oc_col <> oc), CHOOSE({1,2}, INDEX(fd, allr_fd, 1), oc_col), {"|","|"}),
rstr, CONCAT(INDEX(revfd, , 2)),
rary, IFERROR(FILTERXML("<x><y>" & SUBSTITUTE(rstr, "|", "</y><y>") & "</y></x>", "//y"), ""),
ls, LEN(rary),
maxs, MAX(ls),
nmaxs, SUM(--(ls = maxs)),
csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls),
idxs_ls, FILTER(SEQUENCE(ROWS(ls)), ls = maxs),
idxs_dts, INDEX(csum_ls, idxs_ls),
seds, INDEX(FILTER(INDEX(revfd, , 1), INDEX(revfd, , 1) <> "|"), idxs_dts),
dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")),
finres, IF(maxs = 0, {"none","",""}, CHOOSE({1,2,3}, maxs, nmaxs, dt_comp)), finres)

 

[KRice]

That appears to be copied directly from my post (with the t range edited), which works fine in my workbook. I don't see any issue with parentheses. I've eliminated all of the line breaks in the formula in case that might be causing a problem. I meant to mention something earlier: I noticed that your W, D, etc. subsection headings that are used to indicate something about which match outcomes to consider...there is a problem with some of them. They are used to create an exact match with the large data table, but some of those entries are followed by a space (for example, "W " rather than "W")...so nothing will match. Review all of the column subsection headings to confirm that leading or trailing spaces are not present.

Also, to adapt the formula to a table situated in A2:Q17 (the range given to t), keep in mind that the location (loc) variable points to the corresponding match location, whether All, H, or A are desired for the summary table. So the first location entry in an A2:Q17 summary table is probably found in B4 (not B22), like this:

MrExcel_20240321 (version 1) (version 1).xlsx
	A	B	C	D	E	F	G	H
1	ALL-TIME RECORDS
2	CONSECUTIVES		W	Wins		D	Draws
3			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
4	ALL	ALL	7	1	10/03/2024	2	1	14/02/2024
5		H	5	2	2024, 29/02/2024	1	1	13/02/2024
6		A
7	League	ALL
8		H
9		A
10	LONGEST RUNS		NO L			NO D
11			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
12	ALL	ALL
13		H
14		A
15	League	ALL
16		H
17		A
Marvo

Cell Formulas
Range	Formula
C4:H5	C4	=LET(loc, $B4, src, All!$B$2:$M$7000, t, $A$2:$Q$17, a, INDEX(t, , 1), idxCon, MATCH("Consecutives", a, 0), idxLR, MATCH("Longest Runs", a, 0), curridx, ROW() - ROW(INDEX(a, 1)) + 1, dyna, INDEX(a, SEQUENCE(curridx)), mt, INDEX(dyna, XMATCH(TRUE, dyna <> "", 0, -1)), ct, IF(curridx >= idxLR, "Longest Runs", "Consecutives"), oc_input, IF(curridx >= idxLR, INDEX(t, idxLR, COLUMN()), INDEX(t, idxCon, COLUMN())), oc, XLOOKUP(oc_input, {"W","NO L","D","NO D","L","NO W","GF","NO GA","GA","NO GF"}, {"W","L","D","D","L","W","S","S","S","S"}), sect, IFS(OR(oc_input = {"W","D","L","NO L","NO D","NO W"}), 1, OR(oc_input = {"GF","NO GF"}), 2, OR(oc_input = {"GA","NO GA"}), 3), fsrc_cols, CHOOSE(sect, {1,4,9,10}, {1,4,9,11}, {1,4,9,12}), fsrc, FILTER(src, INDEX(src, , 10) <> ""), data, INDEX(fsrc, SEQUENCE(ROWS(fsrc)), fsrc_cols), allr_data, SEQUENCE(ROWS(data), , 1, 0), loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc), mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt), fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1,4}), loc_ary * mt_ary), allr_fd, SEQUENCE(ROWS(fd)), oc_col, IFS(sect = 1, INDEX(fd, , 2), OR(sect = 2, sect = 3), IF(INDEX(fd, , 2) > 0, "S", "N")), revfd, IF(SWITCH(ct, "Consecutives", oc_col = oc, "Longest Runs", oc_col <> oc), CHOOSE({1,2}, INDEX(fd, allr_fd, 1), oc_col), {"|","|"}), rstr, CONCAT(INDEX(revfd, , 2)), rary, IFERROR(FILTERXML("<x><y>" & SUBSTITUTE(rstr, "|", "</y><y>") & "</y></x>", "//y"), ""), ls, LEN(rary), maxs, MAX(ls), nmaxs, SUM(--(ls = maxs)), csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls), idxs_ls, FILTER(SEQUENCE(ROWS(ls)), ls = maxs), idxs_dts, INDEX(csum_ls, idxs_ls), seds, INDEX(FILTER(INDEX(revfd, , 1), INDEX(revfd, , 1) <> "|"), idxs_dts), dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")), finres, IF(maxs = 0, {"none","",""}, CHOOSE({1,2,3}, maxs, nmaxs, dt_comp)), finres)
Dynamic array formulas.

 

[Marvo]

That formula now works in C4. What changes have to be made for it to work in the other cells? In C5 it produces 0.

 

[Marvo]

C4, F4 & I4 all good.

 

[Marvo]

Oh dear, that was going so well.

 

[KRice]

Can you post a small section of your summary table using the XL2BB add-in showing some cells that work and one or two that do not?

 

[Marvo]

I could but I had to remove the formula as I was getting the above error throughout the workbook. I'll make a copy of the workbook, then do as you ask.