Mar 13 2023 09:10 AM
If I could pull from this list:
Any of these cities that appear in this column:
Have each city by name appear in A30 and so on (where if there are 11 Clevelands it just says Cleveland) and in Box B30 be the total for those Clevelands taken from G3 and down
Mar 13 2023 11:53 AM - edited Mar 13 2023 12:46 PM
If you're on 365 and able to upload an anonymized workbook (a few of the screen captures are small) sample, I can draw up the formula for you.
Edit: I've created a sample Demo:
Named items: city, data, uCity (unique list of cities)
Constant: header
Determine if a city is on tax sheet. If so, filter for the city and sum the results. Next, stack the city name next to the total.
=LAMBDA(a,v,IF(
ISERROR(XMATCH(v, City)),
a,
LET(
filtered, FILTER(TAKE(Data, , -1), TAKE(Data, , 1) = v),
VSTACK(a, HSTACK(v, SUM(filtered)))
)
))
'Sheet level function
=REDUCE(Header,uCity,Total)