Counting data from range in mulitple sheets

[circlesecretary]

I have a workbook with multiple sheets (2003) and on my last sheet I need to have the total amount of times each code (R1-R24, A1-A22, etc.) was used within the range of N4:U6 of each sheet.

I thought I found the perfect formula for this here --> http://www.mrexcel.com/forum/showthread.php?t=503785&highlight=count+key+words+multiple+sheets

I assigned a named range for my "Sheets" and came up with this-
=SUMPRODUCT(COUNTIF(INDIRECT("""&Sheets&""!N4:U6"),"R1"))

I got back#REF!. That's when I decided that R1 wouldn't work because it's the name of a cell! So I changed it to "Peer1" and changed "R1" to "Peer1" within range.... but it still says #REF!.

=SUMPRODUCT(COUNTIF(INDIRECT("""&Sheets&""!N4:U6"),"Peer1"))

Any suggestions?

 

[Desu Nota from Columbus]

=SUMPRODUCT(COUNTIF(INDIRECT("""&Sheets&""!N4:U6"),"R1-R24"))


Have you tried this and it didn't work?

Im confused, are you trying to count how many times R1-R24 and A1-A22 occurs?

or

R1, R2, R3...
A1, A2, A3...?

 

[circlesecretary]

Oh, it doesn't look like I explained that part very well. The codes are actually R1, R2, R3.... so with this one I'm actually looking for the total amount of R1 (or Peer1, since I don't think R1 would work).

 

[Greg Truby]

Good research! There's a typo in that formula (your version, not in Brian's original). It needs a single quote, not a double...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!N4:U6"),"R1"))

And the R1 being a valid address don't mean nuthin'. Works just fine looking for "A1" or "R1" for me.

 

[circlesecretary]

That is great! Thank you! That didn't even cross my mind!