# 3D countifs using named ranges

#### captainentropy

##### Board Regular
Until I can get countif.3d to work I have to rely on using something like =SUMPRODUCT(COUNTIF(INDIRECT("'"&NamedSheets&"'!A:A"),"criteria").

But what if my data isn't in the exact same format across all the named sheets? What if, for example, my data is in B11:B1000 in one sheet but in B11:B450 in another? Can I count in those exact ranges?

I tried creating names for each range using the name manager and then using:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&NameA&"'"),"criteria",INDIRECT("'"&NameB&"'"),"criteria"))

but that just gave me a #REF! error.

Any idea if I can do what I'm trying to do?

Thanks!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### pgc01

##### MrExcel MVP
Hi

The way I'd do it is to define the same name as a worksheet name for every worksheet.

This would make it easy to write the formula, as you'd just need the list of the worksheets.

How are you defining your names?

#### captainentropy

##### Board Regular
Hi pgc01, I used the name manager to create the named ranges. Name Manager -> New -> (gave it a name, then selected the range, and repeated for the other sheets).

But the problem is that on each sheet the data that I'm trying to use COUNTIF on is not constant. It's the same column but varying numbers of rows. I start with lists of data and then sort for certain criteria then enter a new row beneath my cutoff. That way I can resort all the columns that meet certain criteria. I can't just delete the data below the newly inserted row, I want to keep it because I need to count those cells for other reasons, otherwise I could just set my range in the formula above to be the whole column (like in the first formula I posted). I'm not sure if I'm being clear. Am I making sense?

I could just name each sheet it's own name. That works, but the problem is that I'm only counting in a certain range of cells in one column, and this varies among my sheets (see above reasoning). I suppose I could just make new sheets of that data specifically but I'm hoping to not have to keep growing this workbook. It's already like 70 MB with 45 sheets.

#### pgc01

##### MrExcel MVP
Hi

I'm not sure I understand it.

I'll give you a small example, and you'll see if it helps.

Let's say you have a the formula like:

=COUNTIFS(A1:A5,"a",B1:B5,"<10")

Now you want to count all the values that meet the criteria in Sheet1, Sheet2, and Sheet3, in the same columns but in different rows, for ex. in Sheet2 it would be rows 5:15 and in Sheet3 it would be 10:15

This is how I would do it.

I would create the worksheet names:

Sheet1 Names:

NameA =Sheet1!\$A\$1:\$A\$5
NameB =Sheet1!\$B\$1:\$B\$5

Sheet2 Names:

NameA =Sheet2!\$A\$5:\$A\$15
NameB =Sheet2!\$B\$5:\$B\$15

Sheet3 Names:

NameA =Sheet3!\$A\$10:\$A\$15
NameB =Sheet3!\$B\$10:\$B\$15

and the workbook name:

Wks ={"Sheet1","Sheet2","Sheet3"}

Now I can use the formula, in Sheet1:D2

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Wks&"'!NameA"),"a",INDIRECT("'"&Wks&"'!NameB"),"<10"))

Does this help?

#### captainentropy

##### Board Regular
Hi pgc,

I recreated your example and I get a #REF! error. For the workbook name do I literally type "={"Sheet1","Sheet2","Sheet3"}" into a cell then hit enter and name that cell "wks"?

#### pgc01

##### MrExcel MVP
Hi pgc,

I recreated your example and I get a #REF! error. For the workbook name do I literally type "={"Sheet1","Sheet2","Sheet3"}" into a cell then hit enter and name that cell "wks"?

Hi

No. Wks is a named constant.

In the Name Manager, you create a new workbook name:

Name: Wks
Scope: Workbook
Refers to: ={"Sheet1","Sheet2","Sheet3"}

I would not have expected a #REF! error, though, just a wrong result.
Make sure all the named ranges are well defined (name, scope and refers to).

#### captainentropy

##### Board Regular
ok, I used the name manger to make that constant. Same error results. The problem must be in the other names. First though, to make that constant, in the "Refers to:" field did you type out "={"Sheet1","Sheet2","Sheet3"}" or use some other method to select those sheets?

So, first I named the range Sheet1!\$A\$1:\$A\$5 as "NameA", but when I tried to name Sheet2!\$A\$5:\$A\$15 "NameA" it wouldn't let me. It just switched back to the named range on Sheet1.

So to get "NameA" to refer to all the ranges on the 3 worksheets what I did was use the name manager and created:

Name: NameA
Scope: Workbook
Refers to: =Sheet1!\$A\$1:\$A\$8,Sheet2!\$A\$5:\$A\$14,Sheet3!\$A\$7:\$A\$14

Then I repeated the same procedure for NameB. No problems at this point, or so it seemed. Maybe that was the wrong thing to do. But when I wrote the formula "=SUMPRODUCT(COUNTIFS(INDIRECT("'"&wks&"'!NameA"),"a",INDIRECT("'"&wks&"'!NameB"),"<10"))" it still returns the #REF! error.

#### pgc01

##### MrExcel MVP
The names are not well defined!

If you read my post again you'll see that I wrote that NameA and NameB are worksheet names, not workbook names.
Only Wks is a workbook name.

So, for ex. for NameA you should define the 3 worksheet names:

Name: NameA
Scope: Sheet1
Refers to: =Sheet1!\$A\$1:\$A\$5

Name: NameA
Scope: Sheet2
Refers to: =Sheet2!\$A\$5:\$A\$15

Name: NameA
Scope: Sheet3
Refers to: =Sheet3!\$A\$10:\$A\$15

You'll have 3 different NameA names, one in each worksheet referring to the range in that same worksheet.

It's similar to the 3 worksheet names NameB

It seems you defined the Wks workbook name ok, you do have to type it in the refers to field as you wrote.

Last edited:

#### captainentropy

##### Board Regular
OK, now it works, thanks! 7 is the answer.

I did read the original post, many times. On Sheet1 I named \$A\$1:\$A\$5 "NameA" and \$B\$1:\$B\$5 "NameB". Then tried to repeat that on Sheet2 and it wouldn't let me. I knew something wasn't named right I just couldn't figure out what/why. Using the name manager it made sense when you said the Scope was Sheet1, etc. I'm not sure how to do this without the name manager. "Scope" means little to me without this context. When I look it up most everything about "Scope" is related to VBA. I did find this that would have solved all my problems Excel Range-Name Roundup

#### pgc01

##### MrExcel MVP
I'm glad everything is working now.

I'm not sure how to do this without the name manager.

You can define the name directly.

For ex., select a cell and in the name box type

Sheet1!Test

Press Enter and you'll see the name Test in the name box.

If you go to the name manager you'll see that the scope of Test is Sheet1.

When you define the name in the name box, if you precede it with the Sheet reference, this tells excel that you want the scope of the name to be that sheet.

Last edited:

Replies
1
Views
108
Replies
5
Views
102
Replies
7
Views
421
Replies
2
Views
105
Replies
17
Views
294

1,172,010
Messages
5,878,711
Members
433,366
Latest member
kaaryy

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

### Which adblocker are you using?

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

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