=IF(AND formula over several worksheets

[Friede]

Dear all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a excel file with over 190 worksheets. I want to make a summary sheet, in which one cell counts the answers over all sheets if in each sheets one cell shows a 1 and another cell shows a two. With the if and function I can do it for each sheet.
<o></o>
=IF(AND(B8=1;B9=2);1;0)
<o></o>
Does somebody know how I can do it with a matrix function over several worksheets?
<o></o>
Thanks in advance for your answer.

 

[jasonb75]

Welcome to the board!

This appears to work with a quick test.

Go to name manager and create a new name,

Name:- Sheets

Refers to:- =GET.WORKBOOK(1)

Then use the formula

=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&Sheets&"'!B8"),1)),--COUNTIF(INDIRECT("'"&Sheets&"'!B9"),2))

to get your result.

 

[Friede]

Thx a lot for your quick reply. Still it does not work properly. I need to count how many of my respondents indicated two specific anwers to two questions. If they answered the first question with a 1 and the second with a 2 the summary sheet should count that as one and this over 190 sheets. I tried to do it with your version and changed it to:

=SUMPRODUCT((COUNTIF(INDIRECT("Respondent"&ROW(1:6)& "!B8");1));COUNTIF(INDIRECT("Respondent"&ROW(1:6)& "!B9");2))

But it did not work. It would be great if you could help me again.

 

[jasonb75]

=SUMPRODUCT(--(COUNTIF(INDIRECT("Respondent"&ROW(1:6)& "!B8");1));--(COUNTIF(INDIRECT("Respondent"&ROW(1:6)& "!B9");2)))

But that only refers to 6 sheets, not 190+ !?

 

[T. Valko]

Welcome to the board!

This appears to work with a quick test.

Go to name manager and create a new name,

Name:- Sheets

Refers to:- =GET.WORKBOOK(1)

Then use the formula

=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&Sheets&"'!B8"),1)),--COUNTIF(INDIRECT("'"&Sheets&"'!B9"),2))

to get your result.

NB...

=GET.WORKBOOK(1) returns ALL sheets including the Summary sheet. If the Summary sheet just happens to meet those conditions it will get included in the count.

Also, if the formula is enterd in either cell B8 or B9 on the Summary sheet then you'll get a circular reference.

 

[Friede]

Dude you are my hero!!! Yes true that was a test for 6 sheets but then it will also work for 190+. Thanks a lot Great job.

BR Friede