Formulas that aggregate by certain criteria

Copper Contributor

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) criteria. I basically need to know how many days our Interior and Exterior work crews were at each city within a specified date range.

Sheet1Sheet2

2 Replies

Re: Formulas that aggregate by certain criteria

You can try Power Query. In the attached file you can add dates and cities in the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh in order to update the green result table.

Re: Formulas that aggregate by certain criteria

``````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: