COUNTIFS Cell Reference Range

M

MS93CP

New Member
Joined
Mar 30, 2017
Messages
11
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Location[/TD]
[TD]Progress[/TD]
[/TR]
[TR]
[TD]Name_1[/TD]
[TD]Type_1[/TD]
[TD]Location_1[/TD]
[TD]Stage_1[/TD]
[/TR]
[TR]
[TD]Name_2[/TD]
[TD]Type_1[/TD]
[TD]Location_3[/TD]
[TD]Stage_2[/TD]
[/TR]
[TR]
[TD]Name_3[/TD]
[TD]Type_2[/TD]
[TD]Location_1[/TD]
[TD]Stage_4[/TD]
[/TR]
[TR]
[TD]Name_4[/TD]
[TD]Type_3[/TD]
[TD]Location_2[/TD]
[TD]Stage_4[/TD]
[/TR]
</tbody>[/TABLE]

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:

Code: 
[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:

Code: 
=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 :)
 
Sort by date Sort by votes

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Question @Nishant94, why does PreferredStages have to be Transposed, but not PreferredLocations? I would imagine either both should be transposed, or none.


Nishant94 said:
Unknown
ABCDEFG
Count
Location_1Stage_3
Location_2Stage_4

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Name[/TD]
[TD="bgcolor: #FAFAFA"]Type[/TD]
[TD="bgcolor: #FAFAFA"]Location[/TD]
[TD="bgcolor: #FAFAFA"]Progress[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #00B0F0, align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Name_1[/TD]
[TD="bgcolor: #FAFAFA"]Type_1[/TD]
[TD="bgcolor: #FAFAFA"]Location_1[/TD]
[TD="bgcolor: #FAFAFA"]Stage_1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Name_2[/TD]
[TD="bgcolor: #FAFAFA"]Type_1[/TD]
[TD="bgcolor: #FAFAFA"]Location_3[/TD]
[TD="bgcolor: #FAFAFA"]Stage_2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Name_3[/TD]
[TD="bgcolor: #FAFAFA"]Type_2[/TD]
[TD="bgcolor: #FAFAFA"]Location_1[/TD]
[TD="bgcolor: #FAFAFA"]Stage_4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Name_4[/TD]
[TD="bgcolor: #FAFAFA"]Type_3[/TD]
[TD="bgcolor: #FAFAFA"]Location_2[/TD]
[TD="bgcolor: #FAFAFA"]Stage_4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Name_5[/TD]
[TD="bgcolor: #FAFAFA"]Type_2[/TD]
[TD="bgcolor: #FAFAFA"]Location_2[/TD]
[TD="bgcolor: #FAFAFA"]Stage_4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Name_6[/TD]
[TD="bgcolor: #FAFAFA"]Type_2[/TD]
[TD="bgcolor: #FAFAFA"]Location_1[/TD]
[TD="bgcolor: #FAFAFA"]Stage_3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0, align: center"]PreferredLocations[/TD]
[TD="bgcolor: #00B0F0, align: center"]PreferredStages[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet13​

[TABLE="width: 943"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 931"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G1[/TH]
[TD]{=SUM(COUNTIFS(B:B,"Type_2",C:C,PreferredLocations,D:D,TRANSPOSE(PreferredStages)))}[/TD]
[/TR]
</tbody>[/TABLE]
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[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 943"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 931"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]PreferredLocations[/TH]
[TD]=Sheet13!$F$11:$F$12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]PreferredStages[/TH]
[TD]=Sheet13!$G$11:$G$12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Click to expand...
 
Last edited:
Upvote 0
FWIW - the SUMPRODUCT() suggestion in post 7 is likely to be more efficient than the COUNTIFS() alternative.
 
Upvote 0
ricardov said:
Question @Nishant94, why does PreferredStages have to be Transposed, but not PreferredLocations? I would imagine either both should be transposed, or none.
Click to expand...

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.
 
Upvote 0
I guess I'm thinking along the logic of 2D array. But yeah, I think somehow it does make sense. Thanks Nishant.

Nishant94 said:
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.
Click to expand...
 
Upvote 0
FormR said:
FWIW - the SUMPRODUCT() suggestion in post 7 is likely to be more efficient than the COUNTIFS() alternative.
Click to expand...

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.
 
Upvote 0
Nishant94 said:
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
Click to expand...

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 :)
 
Last edited:
Upvote 0
FormR said:
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 :)
Click to expand...

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. :)
 
Last edited:
Upvote 0
Nishant94 said:
But sumproduct will always be much less efficient as compared to countifs.
Click to expand...

Hi, Nishant - no offence taken at all :)

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


Excel 2013
ABCDEFGHIJ
1XAKAK1010
2XBLBL1010
3XCMCM1010
4XDNDN1010
5XEOEO1010
6XFPFP1010
7XGQGQ1010
8XHRHR1010
9XISIS1010
10XJTJT1010
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
This one worked a charm!

FormR said:
Hi, you could try like this:

Code: 
=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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
using COUNTIFS to reference multiple cells for criteria for one range.
Replies
1
Views
555
Domenic
D

Forum statistics

Threads
1,223,358
Messages
6,171,624
Members
452,412
Latest member
thomasleysen531

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top