Formulas that aggregate by certain criteria

Question

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

oliverscheurich · Answer

Rex-Delson&nbsp;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.

patrick2788 · Answer

Rex-Delson&nbsp;If you have access to REDUCE, this may work for you.&nbsp;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)&nbsp;&nbsp;

Forum Discussion

Formulas that aggregate by certain criteria

Share

Resources