I have a workbook with multiple sheets of data.
In the first sheet I have a list of values, and I would like to find how many times they appear in the rest of the book. The count would appear in the adjacent cell.
Thank you very much in advance!
-
This is not actually a programming question; you might have more luck on the super user board: superuser.comRayfleck– Rayfleck2012-02-01 01:19:23 +00:00Commented Feb 1, 2012 at 1:19
-
1i didn't understand why my question was getting down voted. I guess that why. I didn't know that stackoverlow is for programming questions only. Well actually this is kind of a programming question. This could be down with vba. Thanks for letting me know. Have a good evening!Yan– Yan2012-02-01 01:41:24 +00:00Commented Feb 1, 2012 at 1:41
-
If this is a VBA question, then you should re-state it that way. Good luck!Rayfleck– Rayfleck2012-02-01 01:46:20 +00:00Commented Feb 1, 2012 at 1:46
-
My guess would be that you are getting downvoted because your question doesn't show any research effort. If you show what you've tried, you'll find that other users are far more receptive.Excellll– Excellll2012-02-01 02:09:25 +00:00Commented Feb 1, 2012 at 2:09
-
1Thanks for the replies. i actually did research and didn't find anything that helps much. I found that i can do =SUM(COUNTIF(INDIRECT("test"&{1,2,3}&"!A1:IV65536"),A1)) which wil go through different sheets but sheets have to be named test 1 2 3 but i have workbook with different sheet names so i would need a way to traverse the whole workbook. i am trying to use the formulas and not use VBAYan– Yan2012-02-01 02:12:47 +00:00Commented Feb 1, 2012 at 2:12
Add a comment
|
1 Answer
You can do it with an extra step and some tinkering with your formula
Define a range name,
AllSheetas=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
(see pic below)To count all occurrences of "Fred" from A1:B10 of all sheets enter in
=SUM(COUNTIF(INDIRECT("'" & AllSheets&"'!A1:B10"),"fred"))
as an array formula (press Ctrl - Shift - Enter together)
the "'" & AllSheets&"'! portion ensures that sheets with spaces etc that produce names like 'My Sheet' will work
Comments
