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 Cleveland) and in Box B30 be the total for those Clevelands taken from G3 and down

 

 

 

Formulas and Functions

1 Reply

  • 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)