Count number of occurrences a value falls between percentages using filtered data set

[Saiyan_8]

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:

• 0-49%
• 50-99%
• 100%
• >100%

Please let me know if you've got any further queries that need explaining and I'll do my best to answer them and thanks in advance for your help with my problem.

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.

 

[Dan Waterloo]

Welcome to the Board, Saiyan!
I'm not sure how the number of spaces and the efficiency are supposed to be related to each other. For example, for Staff Office : Central Support there are 22 spaces. Are a certain number of those 22 spaces being used somehow? Perhaps 14 spaces are being used so this line should somehow fit in the 50% - 99% column? Where on the sheet or on the workbook would you know about the number of used spaces?

 

[Saiyan_8]

Hiya Dan,
Thanks for your reply.
Below is just a small part of the main gathered data. Hopefully it's a bit more clearer to understand.
The 22 spaces are the occurrences Staff Office : Central Support has been picked up by the formula looking in column J (Space type), once I've applied the building number filter. I have another column T working out the theorectical space occupancy taking the area of the space that specific space and this is used to compare to the actual surveyed occupancy in column Q. These two figures divided by each other and shown as a % in column U.

So I wanted to show in the summary those particular figures and let the user know out of 22 Staff Office : Central Support, how many fall in these categories:

• 0-49%
• 50-99%
• 100%
• >100%

SPACE 2020test.xlsx
	C	D	E	F	G	H		J							Q			T	U
1															Offices
2	PROPERTY_REFERENCE	FLOOR	SPACE_REFERENCE	SPACE_NAME	SIGNAGE_TEXT	AREA		SPACE_TYPE							Actual			Open Plan (7.0m²)	Efficiency %
117	106	1	1/001	124AF	Stairs	16.4		Circulation Space : Non Residential
118	106	1	1/002	119AF	Corridor	36.4		Circulation Space : Non Residential
119	106	1	1/003	120	Seminar Room	33.9		Seminar Facilities
120	106	1	1/004	109	Corridor	29.3		Circulation Space : Non Residential
121	106	1	1/005	118	Seminar Room	34.0		Seminar Facilities
122	106	1	1/006	116	Office	15.9		Staff Office or Work Area : Teaching							1			2	50%
123	106	1	1/007	114	Office	14.4		Staff Office or Work Area : Teaching							2			2	100%
2020

Cell Formulas
Range	Formula
T117:T123	T117	=IFERROR(ROUNDDOWN($H117/$S117,0),"")
U117:U123	U117	=IFERROR(Q117/T117,"")

Cells with Data Validation
Cell	Allow	Criteria
T2	List	='Space Norms'!$A$17:$A$20
U2	List	='H:\Space Ut Check\[Theoretical Max Space Occupancy.xlsx]Space Norms'!#REF!

I hope I've explained it ok. I would have uploaded the spreadsheet but read in the guidelines that this is not advised/possible.
Thanks again.

 

[Dan Waterloo]

Sorry, I can't help you. You need to learn how to express your basic problem more clearly.

 

[Saiyan_8]

Sorry Dan but thanks for looking.

However I've think I've stumbled onto something and I think all I need to do now is somehow combine these to formula's efficiently.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(J3:J491,ROW(J3:J491)-MIN(ROW(J3:J491)),,1))*(J3:J491=AL731))
=COUNTIFS($U3:$U657,">=.5",$U3:$U657,"<1")