Excel 2007 using SUMPRODUCT(COUNTIF(INDIRECT to count the value of PASS on all sheets

Hello all,

I have searched for this answer all over the place and have seen a lot of solutions all using different ways and formats to make it work but none have worked for me on Excel 2007. I am new to using any of these formulas.

I have a workbook with about 7 worksheets that all have a particular area (R[13]C[7]:R[76]C[7]) that has a dropdown selection for PASS and FAIL. On the first sheet I want to display the total number of PASS in area R[7]C[7].

Like I said I have seen a lot of formats and tried every one with no success.

The first one was defining a name for the range of sheets so I tried that by defining the name sheets with the value as {...} and refers to set to: =Roles:Auditing. Roles being my first sheet and Auditing being the last sheet in the workbook. I then plugged in the format I found on the web:

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheets'!","'R[-18]C[3]:R[45]C[3]'!"),"PASS"))

This gives a #VALUE! error and does not work.

I also tried this approach but it did not work either:

=SUMPRODUCT(COUNTIF(INDIRECT("'Roles:Auditing'!","!R[-18]C[3]:R[45]C[3]"),"PASS"))

Can someone help me with a formula that will work with Excel 2007?

I also tried others that have single quotes/double quotes/&'s around the defined name etc.

Thanks in advance!!

Originally Posted by NBVC

To go over muliple sheets is not as simple...

You can download and install a free addin, Morefunc.xll

and use this:

=SUMPRODUCT(--(THREED('Roles:Auditing'!R[-18]C[3]:R[45]C[3])="PASS"))

If I use this on the sheet would anyone opening the sheet also need the plugin omn their machien to view it correctly?

Originally Posted by NBVC

An alternative without addins..

Make a list of all sheets in one column and name that range (Insert|Name|Define) something like: TabNames

Then apply

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!R[-18]C[3]:R[45]C[3]"),"PASS"))

When I run it this way I get a #REF! error. When I evaluate the formula I can see it adding in the Tab names and every tab checking the designated area. This looks like it should work.