countif same range across multiple tabs

1. countif same range across multiple tabs

Hi I have a countif formula as follows
=COUNTIF('Jan 10'!B:B,A2)

I have numerous tabs labelled with every month of the year "Jan 10" "Feb 10" etc.

I want the formula to look up the same range on every tab but when I put this formula in it gives a "#value" error.
=COUNTIF('Jan 10:Mar 10'!B:B,A2)

Does anyone know of a way to do this thanks!

2. Re: countif same range across multiple tabs

Hello anthgav,

Countif does not work in 3D. You need Countif.3D, a function available in the free morefunc.xll

3. Re: countif same range across multiple tabs

thanks but unfortunately my works network does not allow the download I will have to download at home and bring in on a stick then I will try it.

4. Re: countif same range across multiple tabs

Format cell A3:A5 as text
In A3: Jan 10
A4: Feb 10
A5: Mar 10
Define name range call "MySheets" no quote in A3:A5

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B:B"),A2))

5. Re: countif same range across multiple tabs

Nice one, Tethless mama!

Interesting to watch this one in the Evaluate Formula tool.

6. Re: countif same range across multiple tabs

Good one theethless mama!

7. Re: countif same range across multiple tabs

John McGimpsey has a quick overview of the options re: conditional 3D calcs:

http://www.mcgimpsey.com/excel/threedsumif.html

I'm sure teethless mama would agree that though elegant the SUMPRODUCT is Volatile, Volatile Arrays & Sumproducts are generally best avoided esp. if used en masse.

Generally speaking the use of intermediate tables is the more efficient approach - though no where near as elegant unfortunately.

8. Re: countif same range across multiple tabs

Thanks for your input every one its greatly appreciated.

the formula that teethless mama gave me is perfect for what I need. Just one more problem to help complete my sheet. I have a cell with this formula

=SUMIF('Jan 10'!B:B,A2,'Jan 10'!E:E)

It sums all the quantities adjacent to the cells that have been counted in the countif formula. But again I want it to sumif across the same multiple sheets as the countif.
Can you help?

9. Re: countif same range across multiple tabs

I understand what your saying DonkeyOte about the sheet being volatile, every time I adjust any data on any tabs the sheet takes ages to re-calculate cells. Perhaps the best solution for me here is to create a summary sheet with every months formulas on its own row and then just sum them all up at the end.
I was trying to be clever by creating a formula that would do it all in one cell without all the extra work but it looks like I will have to.
In the example I only used 3 months but in reality my information goes back well over a year and also the formulas goes down 1500 lines for 1500 different countifs hence why teethless mamas formula became volatile.

I might have a look at "morefunc.xll" and see what thats all about but the sheet is used on multiple computers on a network will that make any difference using "morefunc.xll"

Still open to any suggestions though!

10. Re: countif same range across multiple tabs

Originally Posted by teylyn
Hello anthgav,

Countif does not work in 3D. You need Countif.3D, a function available in the free morefunc.xll

Hello,

I am also trying to use COUNTIF() across multiple sheets and was hoping to utilize COUNTIF.3D. Thank you for providing this link, but I downloaded morefunc from here, and I still can't seem to use COUNTIF.3D. I can see the Morefunc menu in my add formula window, but COUNTIF.3D does not appear there. From what I've been able to find out about this function, it appears in Morefunc 5.05; the link above appears to be Morefunc 5 (perhaps 5.0?).

11. Re: countif "0" across multiple tabs

I'm trying to count cells across multiple tabs (113) that contain the number 0. I've tried =COUNTIF('Lastname, firstname1':'Lastname, fristname 113'!E6,"*" & "0" & "*") and get error message. Any help??

12. Re: countif same range across multiple tabs

DCACTT welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1