COUNTIFS Cell Reference Range

[MS93CP]

Hi there! Hoping you can help out

I'm trying to get an array into the criteria of a COUNTIFS function.

Here is a sample of the data:

Name	Type	Location	Progress
Name_1	Type_1	Location_1	Stage_1
Name_2	Type_1	Location_3	Stage_2
Name_3	Type_2	Location_1	Stage_4
Name_4	Type_3	Location_2	Stage_4

<tbody>
</tbody>

I'd like a COUNT of how many rows are of a particular 2, and fall within a set of preferred locations and preferred stages.

In this example, the preferred locations are Location_1 and Location_2 and the preferred stages are Stage_3 and Stage_4.

Currently, I'm having to use multiple lines:

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]COUNTIFS[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]B:B[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]"Type_2"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]C:C[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][FONT=Inconsolata][COLOR=#008000]"Location_1"[/COLOR][/FONT][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]D:D[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][FONT=Inconsolata][COLOR=#008000]"Stage_3"[/COLOR][/FONT][COLOR=#000000][FONT=Inconsolata])
[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]+COUNTIFS([COLOR=#F7981D]B:B[/COLOR],[COLOR=#7E3794]"Type_2"[/COLOR],[COLOR=#11A9CC]C:C[/COLOR],[COLOR=#008000]"Location_1"[/COLOR],[COLOR=#A61D4C]D:D[/COLOR],[COLOR=green]"Stage_4"[/COLOR])
+COUNTIFS([COLOR=#F7981D]B:B[/COLOR],[COLOR=#7E3794]"Type_2"[/COLOR],[COLOR=#11A9CC]C:C[/COLOR],[COLOR=#008000]"Location_2"[/COLOR],[COLOR=#A61D4C]D:D[/COLOR],[COLOR=green]"Stage_3"[/COLOR])
[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]+[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]COUNTIFS[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]B:B[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]"Type_2"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]C:C[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][FONT=Inconsolata][COLOR=#008000]"Location_2"[/COLOR][/FONT][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]D:D[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Stage_4"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]
[/FONT][/COLOR]

This would return an answer of 1 which would be from Name_3.

The aim is to have in another sheet, a table with 2 headers; PreferredLocations and PreferredStages, listing below the relevant info.

I'm hoping you'll be able to help in condensing the above function down into a more dynamic function in a single line:

=COUNTIFS([COLOR=#F7981D][FONT=Inconsolata]B:B[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]"Type_2"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]C:C[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][FONT=Inconsolata][COLOR=#008000]PreferredLocations[/COLOR][/FONT][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]D:D[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]PreferredStages)[/FONT][/COLOR]

But the above doesn't work because of the Named Ranges 'PreferredLocations' and 'PreferredStages'. Using Cell Ranges instead of Names Ranges encounters the same error.

The closest similar question I've found was at https://www.mrexcel.com/forum/excel...s-function-criteria-cell-reference-range.html but I couldn't get it to work for me.

I suspect the answer will be some sort of SUMPRODUCT or QUERY.

Really looking forward to hearing any suggestions/advice!

Thanks

 

[Joe4]

Sounds like you want to apply OR logic to your Criteria argument.
This link shows you how you can do that: https://exceljet.net/formula/countifs-with-multiple-criteria-and-or-logic

 

[Weazel]

just a thought but i don't think that would work Joe because of the number of criteria ranges.

would probably need to be something like....

=SUMPRODUCT((--(B2:B5="type_2")*((C2:C5="Location_1")+(C2:C5="Location_2")*(D2:D5="stage_3")+(D2:D5="stage_4")>0)))

 

[Joe4]

just a thought but i don't think that would work Joe because of the number of criteria ranges.

Hmmm... It appears that you may be right about that. I overlooked the fact that there are two ranges with OR conditions, and that does not seem to work.

Currently, I'm having to use multiple lines:

Note that it is just one formula though. Either way, you have a long formula (though I suspect the SUMPRODUCT formula may be a bit more efficient).

 

[MS93CP]

just a thought but i don't think that would work Joe because of the number of criteria ranges.

would probably need to be something like....

=SUMPRODUCT((--(B2:B5="type_2")*((C2:C5="Location_1")+(C2:C5="Location_2")*(D2:D5="stage_3")+(D2:D5="stage_4")>0)))

I see what you mean. What I'm really after is for the criteria to be a range.

Using your formula, that'd be something like:

=SUMPRODUCT((--(B2:B5="type_2")*((C2:C5="[Cell Reference to range containing below table such as: 'OtherTab'!A2:A5]")*(D2:D5="[Cell Reference to range containing below table such as: 'OtherTab'!B2:B5]")>0)))

So in another sheet (same workbook obviously) I'd have a table such as:

Preferred Locations	Preferred Stages
Location_1	Stage_3
Location_2	Stage_4

<tbody>
</tbody>

This would then allow the criteria to be updated, by simply updating the table above. But the formula doesn't seem to work when a cell range is used like above.

So if at some point I decided I wanted to add Location_3 as a preferred location, I could simply update the above table, rather than having to update the formulae itself.

 

[Nishant94]

Unknown
	A	B	C	D	E	F	G
1	Name	Type	Location	Progress		Count	3
2	Name_1	Type_1	Location_1	Stage_1
3	Name_2	Type_1	Location_3	Stage_2
4	Name_3	Type_2	Location_1	Stage_4
5	Name_4	Type_3	Location_2	Stage_4
6	Name_5	Type_2	Location_2	Stage_4
7	Name_6	Type_2	Location_1	Stage_3
8
9
Sheet13

Cell Formulas
Range	Formula
G1	=SUM(COUNTIFS(B:B,"Type_2",C:C,{"Location_1";"Location_2"},D:D,{"Stage_3","Stage_4"}))

 

[FormR]

What I'm really after is for the criteria to be a range.

Hi, you could try like this:

=SUMPRODUCT(--(B2:B100="Type_2"),--ISNUMBER(MATCH(C2:C100,PreferredLocations,0)),--ISNUMBER(MATCH(D2:D100,PreferredStages,0)))

EDIT: Note, you should avoid using full column references with this type of formula.

 

[Nishant94]

Unknown
	A	B	C	D	E	F	G
1	Name	Type	Location	Progress		Count	3
2	Name_1	Type_1	Location_1	Stage_1
3	Name_2	Type_1	Location_3	Stage_2
4	Name_3	Type_2	Location_1	Stage_4
5	Name_4	Type_3	Location_2	Stage_4
6	Name_5	Type_2	Location_2	Stage_4
7	Name_6	Type_2	Location_1	Stage_3
8
9
10						PreferredLocations	PreferredStages
11						Location_1	Stage_3
12						Location_2	Stage_4
13
Sheet13

Cell Formulas
Range	Formula
G1	{=SUM(COUNTIFS(B:B,"Type_2",C:C,PreferredLocations,D:D,TRANSPOSE(PreferredStages)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
PreferredLocations	=Sheet13!$F$11:$F$12
PreferredStages	=Sheet13!$G$11:$G$12

 

[ricardov]

The answer may be correct, but it seems like the calculation is incorrect. Did you try using Evaluate Formula if the array produced before the sum is correct? I used the first source data sample and it was giving me SUM({0,1,0,0}) when it should be SUM({0,0,1,0})

Unknown

	A	B	C	D	E	F	G
1	Name	Type	Location	Progress		Count	3
2	Name_1	Type_1	Location_1	Stage_1
3	Name_2	Type_1	Location_3	Stage_2
4	Name_3	Type_2	Location_1	Stage_4
5	Name_4	Type_3	Location_2	Stage_4
6	Name_5	Type_2	Location_2	Stage_4
7	Name_6	Type_2	Location_1	Stage_3
8
9
10						PreferredLocations	PreferredStages
11						Location_1	Stage_3
12						Location_2	Stage_4
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13​

Array Formulas Cell Formula G1 {=SUM(COUNTIFS(B:B,"Type_2",C:C,PreferredLocations,D:D,TRANSPOSE(PreferredStages)))} <thead> </thead><tbody> </tbody> Enter the formula with "Ctrl+Shift+Enter". Only Pressing "Enter" will result in formula giving error or incorrect result. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To PreferredLocations =Sheet13!$F$11:$F$12 PreferredStages =Sheet13!$G$11:$G$12 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Nishant94]

The answer may be correct, but it seems like the calculation is incorrect. Did you try using Evaluate Formula if the array produced before the sum is correct? I used the first source data sample and it was giving me SUM({0,1,0,0}) when it should be SUM({0,0,1,0})

Sorry but your concept of this is wrong. Countif is finding combo in this way:

Unknown
	F	G	H	I
15	Type	Location	Progress	Count
16	Type_2	Location_1	Stage_3	0
17	Type_2	Location_1	Stage_4	1
18	Type_2	Location_2	Stage_3	0
19	Type_2	Location_2	Stage_4	0
Sheet13

for this sample;

Unknown
	A	B	C	D
1	Name	Type	Location	Progress
2	Name_1	Type_1	Location_1	Stage_1
3	Name_2	Type_1	Location_3	Stage_2
4	Name_3	Type_2	Location_1	Stage_4
5	Name_4	Type_3	Location_2	Stage_4
Sheet13