Hi Community,
I've decided to post my excel conundrum because I'm just stuck and I can't find the exact work around to my situation. However I'm hopeful someone from this community can solve.
I've indicated part of my spreadsheet below and what I'm trying to do is create a small dynamic (changes once I change the filtered building number) summary table informing me of the number of a particular fields that match the required fields (coloured to make it more prominent).
The data will ideally be filtered by column C (Building Number), I've managed to work the summary table to return the "count" number of occurrences that match the exact fields (in a separate column). I wanted to further manipulate the data (using the efficiency "column U" that has already worked this out) and inform me of how many out of these particular occurrences that are:
I've decided to post my excel conundrum because I'm just stuck and I can't find the exact work around to my situation. However I'm hopeful someone from this community can solve.
I've indicated part of my spreadsheet below and what I'm trying to do is create a small dynamic (changes once I change the filtered building number) summary table informing me of the number of a particular fields that match the required fields (coloured to make it more prominent).
The data will ideally be filtered by column C (Building Number), I've managed to work the summary table to return the "count" number of occurrences that match the exact fields (in a separate column). I wanted to further manipulate the data (using the efficiency "column U" that has already worked this out) and inform me of how many out of these particular occurrences that are:
- 0-49%
- 50-99%
- 100%
- >100%
SPACE 2020test.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
AL | AM | AN | AO | AP | AQ | AR | |||
728 | (0%,49%) | >=0.5,<1 | 100% | >=100% | |||||
729 | Summary | Number of Spaces | Efficiency | ||||||
730 | Building | 106 | 0-50% | 50-99% | 100% | 100%+ | |||
731 | Staff Office : Central Support | 22 | |||||||
732 | Staff Office : Commercial | 0 | |||||||
733 | Staff Office : Research | 12 | |||||||
734 | Staff Office or Work Area : Teaching | 92 | |||||||
735 | PG Student Space : Research | 2 | |||||||
740 | Meeting Rooms | 8 | |||||||
743 | Seminar Facilities | 8 | |||||||
747 | Workshops | 53 | |||||||
750 | IT Laboratories | 3 | |||||||
2020 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AQ728 | AQ728 | =100% |
AM730 | AM730 | =INDEX(C4:C658,MIN(IF(SUBTOTAL(3,OFFSET(C4,ROW(C4:C658)-ROW(C4),0)),ROW(C4:C658)-ROW(C4)+1))) |
AN731:AN735 | AN731 | =SUMPRODUCT(SUBTOTAL(3,OFFSET(J3:J491,ROW(J3:J491)-MIN(ROW(J3:J491)),,1))*(J3:J491=AL731)) |
AN740 | AN740 | =SUM(AN736:AN739) |
AN743,AN750 | AN743 | =SUM(AN741:AN742) |
AN747 | AN747 | =SUM(AN744:AN746) |
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. |