Forum Discussion

johnsboxftm's avatar
johnsboxftm
Brass Contributor
Mar 13, 2023

SUMIFS, TEXT ETC

  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...
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Mar 13, 2023

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)