Count if range contains text within another range

[Costtx]

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

 

[KRice]

What are the lengths of your two ranges (Region and Status)? COUNTIF is expecting that within your data table, the range named Region has some size (probably a start row and end row), and the range named Status should have the same start and end points. Then COUNTIF will examine which rows/columns satisfy all of the matching criteria. I see no issue with your formula in red (you could eliminate the = sign "Central" rather than "=Central", but that is minor. I suspect the issue is how the named ranges are being used.

As an experiment, try substituting explicit cell ranges with equal start and end points for Region and Status...for example:
=SUM(COUNTIFS(A2:A10,"Central",D2:D10,{"New","Work in Progress","Awaiting Approval"}))
...or whatever cell references hold the relevant content.

Also, what version of Excel are you using?

 

[Costtx]

What are the lengths of your two ranges (Region and Status)? COUNTIF is expecting that within your data table, the range named Region has some size (probably a start row and end row), and the range named Status should have the same start and end points. Then COUNTIF will examine which rows/columns satisfy all of the matching criteria. I see no issue with your formula in red (you could eliminate the = sign "Central" rather than "=Central", but that is minor. I suspect the issue is how the named ranges are being used.

As an experiment, try substituting explicit cell ranges with equal start and end points for Region and Status...for example:
=SUM(COUNTIFS(A2:A10,"Central",D2:D10,{"New","Work in Progress","Awaiting Approval"}))
...or whatever cell references hold the relevant content.

Also, what version of Excel are you using?

Hey KRice,

Thank you for the advice. I tried the explicit cell ranges, and it didn't work for me. I am using excel 2016.

 

[KRice]

Could you post a small portion of your worksheet to illustrate the issue, ideally using XL2BB?...something like this:

Book1
	B	C	D	E
1
2				Count
3	A	New		2
4	B	New
5	A	Review
6	B	Old
7	B	New
8	B	Review
Sheet4

Cell Formulas
Range	Formula
E3	E3	=SUM(COUNTIFS($B$3:$B$8,"B",$C$3:$C$8,{"Review","Old"}))

 

[Costtx]

Hi Kirk,

As requested:

Formula of C6 (Functioning as it should)
=SUM(COUNTIFS(Region,"Central",Status,{"New","Work in Progress","Customer Replied","Awaiting Customer Info","Awaiting 3rd Party","Awaiting Internal","Awaiting Approval"}))

Formula of D6 (Not functioning)
=SUM(COUNTIFS(Requestor_Location,CentralS,Status,{"New","Work in Progress","Customer Replied","Awaiting Customer Info","Awaiting 3rd Party","Awaiting Internal","Awaiting Approval"}))

 

[KRice]

Is "CentralS" found anywhere in the Requestor_Location column? If so, and if that is supposed to be one of the criteria, then it needs to be enclosed in quotes. Also, I see the formula uses a "_" between Requestor and Location, and the table heading (those are named table headings, right?)...does not.

 

[Costtx]

Sorry, Even with the " " the CentralS didn't work. CentralS is meant to be a range. I took the list from Central, duplicated it and removed the locations which I didn't want counted. The heading for Requestor Location does not contain the "_" while the formula does.

 

[KRice]

I can't tell what specifically is being referred to by "Requestor_Location" and "Status". Did you assign a name to this data table and are those table headings...like this example, where the table is called Table1:

Book2
	A	B	C	D	E	F
1	Status	Requestor Location	Region		Count	2
2	in progress	CentralS	Central
3	new resolved	there	Central
4	customer replied	CentralS	Central
5	new resolved	there	Northern
6	New	CentralS	Central
7	new resolved	there	Central
Sheet1

Cell Formulas
Range	Formula
F1	F1	=SUM(COUNTIFS(Table1[Requestor Location],"CentralS",Table1[Status],{"New","Work in Progress","Customer Replied","Awaiting Customer Info","Awaiting 3rd Party","Awaiting Internal","Awaiting Approval"}))

I am assuming not...it sounds as if you've named ranges and for that part of your formula, you want to consider any rows where the Requestor_Location column includes an element in your CentralS range...is that correct? If so, I think you'll have to explicity list those elements in the CentralS range as criteria, rather than referring to the CentralS range as the criteria.

 

[KRice]

You might try something like this:

MrExcel20200508.xlsx
	A	B	C	D	E	F
1	Status	Requestor Location	Region
2	work in progress	org design	Central		Count	5
3	new	org design	Central
4	resolved	org design	Northern
5	new	headquarters	Northern
6	new	hr rock	Central
7	resolved	hr rock	Central
8	resolved	hr rock	Central
9	customer replied	hr rock	Central
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(COUNTIFS(Table1[Requestor Location],{"org design","hr rock","headquarters"},Table1[Status],{"New";"Work in Progress";"Customer Replied";"Awaiting Customer Info";"Awaiting 3rd Party";"Awaiting Internal";"Awaiting Approval"}))

Where I've used table headings (e.g., Table1[Requestor Location]), you could use your named range instead, but the criteria to be found in that range (or under that column heading in my example) need to be explicitly listed.
Also note that one of the arrays uses elements separated by commas and the other uses semicolons. This is due to how COUNTIFS treats these arrays, considering one as a single column array and the other as a single row array.