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

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
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)))
 
Upvote 0
just a thought but i don't think that would work Joe because of the number of criteria ranges.
Click to expand...
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:
Click to expand...
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).
 
Last edited:
Upvote 0
Weazel said:
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)))
Click to expand...

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:

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Preferred Locations[/TD]
[TD]Preferred Stages[/TD]
[/TR]
[TR]
[TD]Location_1[/TD]
[TD]Stage_3[/TD]
[/TR]
[TR]
[TD]Location_2[/TD]
[TD]Stage_4[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0

Unknown
ABCDEFG
1NameTypeLocationProgressCount3
2Name_1Type_1Location_1Stage_1
3Name_2Type_1Location_3Stage_2
4Name_3Type_2Location_1Stage_4
5Name_4Type_3Location_2Stage_4
6Name_5Type_2Location_2Stage_4
7Name_6Type_2Location_1Stage_3
8
9
Sheet13
Cell Formulas
RangeFormula
G1=SUM(COUNTIFS(B:B,"Type_2",C:C,{"Location_1";"Location_2"},D:D,{"Stage_3","Stage_4"}))
 
Last edited:
Upvote 0
MS93CP said:
What I'm really after is for the criteria to be a range.
Click to expand...

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.
 
Last edited:
Upvote 0

Unknown
ABCDEFG
1NameTypeLocationProgressCount3
2Name_1Type_1Location_1Stage_1
3Name_2Type_1Location_3Stage_2
4Name_3Type_2Location_1Stage_4
5Name_4Type_3Location_2Stage_4
6Name_5Type_2Location_2Stage_4
7Name_6Type_2Location_1Stage_3
8
9
10PreferredLocationsPreferredStages
11Location_1Stage_3
12Location_2Stage_4
13
Sheet13
Cell Formulas
RangeFormula
G1{=SUM(COUNTIFS(B:B,"Type_2",C:C,PreferredLocations,D:D,TRANSPOSE(PreferredStages)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
PreferredLocations=Sheet13!$F$11:$F$12
PreferredStages=Sheet13!$G$11:$G$12
 
Upvote 0
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})

Nishant94 said:
Unknown
ABCDEFG
Count
Location_1Stage_3
Location_2Stage_4

<colgroup><col style="width: 25pxpx"><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: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G1[/TH]
[TD="align: left"]{=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: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]PreferredLocations[/TH]
[TD="align: left"]=Sheet13!$F$11:$F$12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]PreferredStages[/TH]
[TD="align: left"]=Sheet13!$G$11:$G$12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
ricardov said:
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})
Click to expand...

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


Unknown
FGHI
15TypeLocationProgressCount
16Type_2Location_1Stage_30
17Type_2Location_1Stage_41
18Type_2Location_2Stage_30
19Type_2Location_2Stage_40
Sheet13


for this sample;


Unknown
ABCD
1NameTypeLocationProgress
2Name_1Type_1Location_1Stage_1
3Name_2Type_1Location_3Stage_2
4Name_3Type_2Location_1Stage_4
5Name_4Type_3Location_2Stage_4
Sheet13
 
Last edited:
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,625
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