MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF Question - how to work around the 3-d reference 'limitation'

Posted by Tom Sutton on July 25, 2001 5:53 AM

I have a perfect case for needing the ability to use a 3-d reference with the COUNTIF function. As I have already discovered in a previous post, 3-d references within COUNTIF are apparently not allowed. Here's my situation:
I have multiple worksheets based on a single template. Users place an "X" to indicate they have or want something. I need a summary worksheet that indicates how many of each item people have or want. For example, on cell I5 of each template they would place an X if they wanted something, and leave it blank if they didn't want it. The summary worksheet needs to cound how many Xs are in cell I5 of all worksheets. I tried '=COUNTIF(Beg:End!I5,"X")' where "beg" and "end" are the worksheets that delimit where the inputs are located.

Anyone have a workaround or possibly a way to use COUNT and IF or SUM and IF together to achieve this result? I want to avoid formulas that have each input worksheet name hard-coded if possible since the number and names of input worksheets can vary quite a bit.

Thanks in advance for any help you can offer!

Posted by Mark W. on July 25, 2001 4:23 PM