I would like to create a formula which counts data if it matches the criteria layed out in multiple cell ranges.
Range 1 - Region
Central
Far North
Metropolitan
North Coast
North
South
each region will contain these locations among others:
School
High School
Kindergarten
City Hall
Shopping Centre
Health Centre
Hospital
(various other similar locations)
Range 2 - Status
New
Awaiting Approval
Work in Progress
Resolved
Closed
I have a cell for each region and would like to have a formula which counts how many of the locations in each region has a status of new, awaiting approval and work in progress.
I then have a separate cell which counts how many locations in each region has a status of resolved and closed.
I have used the following formulas which have worked in similar counting functions within the same workbook
=SUM(COUNTIFS(Region,"=Central",Status,{"New","Work in Progress","Awaiting Approval"}))
I have tried to tweak it a bit to further so it performs the same function but with specific locations within the region range. I have tried creating a separate range including those specific location, problem is that every time i try it just returns a value of zero.
Any suggestions?
Thanks
Range 1 - Region
Central
Far North
Metropolitan
North Coast
North
South
each region will contain these locations among others:
School
High School
Kindergarten
City Hall
Shopping Centre
Health Centre
Hospital
(various other similar locations)
Range 2 - Status
New
Awaiting Approval
Work in Progress
Resolved
Closed
I have a cell for each region and would like to have a formula which counts how many of the locations in each region has a status of new, awaiting approval and work in progress.
I then have a separate cell which counts how many locations in each region has a status of resolved and closed.
I have used the following formulas which have worked in similar counting functions within the same workbook
=SUM(COUNTIFS(Region,"=Central",Status,{"New","Work in Progress","Awaiting Approval"}))
I have tried to tweak it a bit to further so it performs the same function but with specific locations within the region range. I have tried creating a separate range including those specific location, problem is that every time i try it just returns a value of zero.
Any suggestions?
Thanks