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!
Hello anthgav,
Countif does not work in 3D. You need Countif.3D, a function available in the free morefunc.xll
Download link: http://download.cnet.com/Morefunc/30...-10423159.html
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.
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))
Nice one, Tethless mama!
Interesting to watch this one in the Evaluate Formula tool.
Good one theethless mama!
Chaitanya
Excel Expert and Excel Consultant
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.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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?
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!
Last edited by anthgav; 04-13-2010 at 06:17 PM.
Hello,Originally Posted by teylyn
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?).
Am I missing something? If so I would really appreciate any help anyone can provide. I tried downloading directly from what I thought was the author's website, but the download link seems to be broken there. http://xcell05.free.fr/morefunc/english/#Download If anyone has an alternate link it would be much appreciated.
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??
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.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new 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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks