# INDIRECT in SUMPRODUCT for sheets with different name

#### shuffleyourfeet

##### New Member
Hello,

I work on four sheets on excel with the same layout. Each sheet contains two columns, A: users and B: country of origin. I created these 4 sheets in order to categorize the users according to role (eg: Musicians, Public, Music Sites etc).

I also created a 5th sheet to do my analytics. For example I wanted to count how many people, of all the users, came from Germany. Therefore i used an indirect formula to import data from all 4 sheets. This formula was:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!B1:B40"),"germany"))

It worked. Now, in order to make it more presentable I renamed the sheets according to role. So now

Sheet1=Musicians
Sheet2=Public

and so on. Which formula should I use in order to get the same results after this alteration?
The abovementioned formula doesn't work. I tried different combinations but still got errors. Any suggestions?

Thanks!

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Welcome to the board.

Make a list of your sheetnames in a range, say F1:F4 (F1=Musicians, F2=Public, etc)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$F\$1:\$F\$4&"'!B1:B40"),"germany"))

Welcome to the board.

Make a list of your sheetnames in a range, say F1:F4 (F1=Musicians, F2=Public, etc)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$F\$1:\$F\$4&"'!B1:B40"),"germany"))

Jonmo1, thank you very much for your reply. However the formula gives me an error (#REF). Is the formula missing sth?

Make sure you have valid sheet names in all cells in F1:F4
No blanks either.

Ha! You're right. One of the sheet names had an extra space.

I was just wandering, out of curiosity, is there another method closer to my previous formula that can also work?
Like, with my formula being:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!B1:B40"),"germany"))

when I used only one category, the "musicians" one it worked

=SUMPRODUCT(COUNTIF(INDIRECT("Musicians"!B1:B40"),"germany"))

When I tried to combine it with the "public" category, it failed

=SUMPRODUCT(COUNTIF(INDIRECT("Musicians"&"Public""!B1:B40"),"germany"))

Many thanks again, it helped a lot!

Perhaps

=SUMPRODUCT(COUNTIF(INDIRECT("""&{"Musicians","Public"}&"'!B1:B40"),"germany"))

It does't seem to work but it doesn't matter. The one you gave me before works absolutely fine! Just a final question:

Suppose we have 2 columns again, users and countries and want to make a countif analysis on a different sheet. Again we want to measure how many users come from germany. Until now, when I did a countif analysis on the same sheet with the rest of the data i simply typed the name of the variable within " ". In this case where I'm using a separate sheet, the commands start with a Sheet1! but when I type Sheet1!"germany" it gives me an error. Is there another way instead of manually searching the variable in the shels?

Cheers!

I had a typo in there

=SUMPRODUCT(COUNTIF(INDIRECT("""&{"Musicians","Public"}&"'!B1:B40"),"germany"))
should be
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Musicians","Public"}&"'!B1:B40"),"germany"))

Sorry, don't quite understand the new requirement..

2 columns, Users and Country on Sheet1.
I want to make my analysis on Sheet2 to make it more presentable. I want to measure how many time a country appears in my sample. However, when I use the countif demand on Sheet2 for the data on Sheet1 it doesn't let me use " " to define my criteria, as it would do if i worked on the same sheet. It only allows me to click on it manually from sheet1.

the image i get is:

range: Sheet1!B:B
criteria: Sheet1!B10

and formula
=COUNTIF(Sheet1!B:B,Sheet1!B10)

I just want to avoid clicking on the cell on sheet1. Instead, I want type the criteria im searching for. It saves me time from going to sheet1, scrolling down and searching for the cell. Any ideas?

Is it

=COUNTIF(Sheet1!B:B,"Criteria") ?

Replies
1
Views
164
Replies
2
Views
246
Replies
17
Views
989
Replies
6
Views
553
Replies
4
Views
160

1,196,048
Messages
6,013,093
Members
441,747
Latest member
darkman77

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