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

 

[ricardov]

Ahh!!! Dang, that one's really good. Big kudos on that Nishant!

 

[ricardov]

Question @Nishant94, why does PreferredStages have to be Transposed, but not PreferredLocations? I would imagine either both should be transposed, or none.

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><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>

 

[FormR]

FWIW - the SUMPRODUCT() suggestion in post 7 is likely to be more efficient than the COUNTIFS() alternative.

 

[Nishant94]

Question @Nishant94, why does PreferredStages have to be Transposed, but not PreferredLocations? I would imagine either both should be transposed, or none.

Yes any one of them can be transposed. This logic behind this is its merging one preferred location with two stages. If we won't have transposed it then, it won't use all the combo. So, location 1 is merging with Stage 3 and Stage 4 and

Location 2 with Stage3 & Stage 4.

Hope that helps.

 

[ricardov]

I guess I'm thinking along the logic of 2D array. But yeah, I think somehow it does make sense. Thanks Nishant.

Yes any one of them can be transposed. This logic behind this is its merging one preferred location with two stages. If we won't have transposed it then, it won't use all the combo. So, location 1 is merging with Stage 3 and Stage 4 and

Location 2 with Stage3 & Stage 4.

Hope that helps.

 

[Nishant94]

FWIW - the SUMPRODUCT() suggestion in post 7 is likely to be more efficient than the COUNTIFS() alternative.

I have tested the speed of both the countifs and sumproduct formula using Fastexcel v3 and countif is calculating way more faster than the solution provided in post #7

Obviously the ranges matter and he can definately reduce it according to his need.

 

[FormR]

I have tested the speed of both the countifs and sumproduct formula using Fastexcel v3 and countif is calculating way more faster than the solution provided in post #7

Interesting, did you test exactly as you have posted with the full column references - how many rows of data did you include?

Either way - I doubt there is too much overhead with either and the OP now has a couple of options to choose from

 

[Nishant94]

Interesting, did you test exactly as you have posted with the full column references - how many rows of data did you include?

Either way - I doubt there is too much overhead with either and the OP now has a couple of options to choose from

I have tested both the options with same range.
You can test both the options. But sumproduct will always be much less efficient as compared to countifs.

And the range is subjective. With same range sumproduct is extremely slow as compared to countifs.

Just want to share it as a knowledge.
Please don't take it as a offense. I have full respect of your knowledge.

 

[FormR]

But sumproduct will always be much less efficient as compared to countifs.

Hi, Nishant - no offence taken at all

Could you maybe test this exact set-up with fast excel?

Excel 2013
	A	B	C	D	E	F	G	H	I	J
1	X	A	K		A	K		10		10
2	X	B	L		B	L		10		10
3	X	C	M		C	M		10		10
4	X	D	N		D	N		10		10
5	X	E	O		E	O		10		10
6	X	F	P		F	P		10		10
7	X	G	Q		G	Q		10		10
8	X	H	R		H	R		10		10
9	X	I	S		I	S		10		10
10	X	J	T		J	T		10		10
Sheet1

Cell Formulas
Range	Formula
J1	=SUMPRODUCT(--($A$1:$A$10="X"),--ISNUMBER(MATCH($B$1:$B$10,$E$1:$E$10,0)),--(ISNUMBER(MATCH($C$1:$C$10,$F$1:$F$10,0))))
H1	{=SUM(COUNTIFS($A$1:$A$10,"X",$B$1:$B$10,$E$1:$E$10,$C$1:$C$10,TRANSPOSE($F$1:$F$10)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MS93CP]

This one worked a charm!

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.