Forum Discussion

Rex-Delson's avatar
Rex-Delson
Copper Contributor
Jan 19, 2023

Formulas that aggregate by certain criteria

I need formulas in Sheet2 Columns B,C that will look at the data in Sheet1 B4:E13 (Cities) and show the number of times we were there based on Sheet1 B3:E3 (Type of Work - Interior or Exterior) crite...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Jan 19, 2023

Rex-Delson 

If you have access to REDUCE, this may work for you.

 

Array constant 'Header'
={"Location", "Number of Exterior Days", "Number of Interior Days"}

Named item 'grid' - this is where the locations are entered.
=Sheet1!$B$4:$E$1000

Named item 'locations' - this converts the grid to a column, pulls blanks and dupes, and sorts A to Z.
=SORT(UNIQUE(TOCOL(grid, 1)))

'Tally' Lambda - This takes each location and obtains a count for each half of the grid - interior and exterior.  It stacks the city name with these counts.
=LAMBDA(a,v,LET(
    exterior, COUNTIF(TAKE(grid, , -2), v),
    interior, COUNTIF(TAKE(grid, , 2), v),
    VSTACK(a, HSTACK(v, exterior, interior))
))

Sheet level formula:
=REDUCE(Header,Locations,Tally)

 

 

Resources