INDIRECT in SUMPRODUCT for sheets with different name

[shuffleyourfeet]

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!

 

[Jonmo1]

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

 

[shuffleyourfeet]

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?

 

[Jonmo1]

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

 

[shuffleyourfeet]

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!

 

[Jonmo1]

Perhaps

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

 

[shuffleyourfeet]

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!

 

[Jonmo1]

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

 

[shuffleyourfeet]

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?

 

[Jonmo1]

Is it

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