Peo,
Thanks for your reply. I struggled with SUMPRODUCT (trying to use
INDIRECT with list of sheet names) and failed;or perhaps (more positively) I
discovered in my trials that it couldn't be done!
Not least it exposed my ignorance regarding formulae.... but I guess that's
part of the learning cycle.
Thanks again.
"Peo Sjoblom" wrote:
It may not be possible using a single formula, probably by using a
sumproduct formula for each single
sheet and then total them. To be honest a layout like this is not very good
if one needs to have conditional counts on each sheet..
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"Toppers" wrote in message
...
How would you do this without the add-in?
"Aladin Akyurek" wrote:
Let B1:F1 on the summary sheet house the names of interest, A2, A3, A4,
downwards the four digit numbers.
If you have the free morefunc.xll dd-in installed, insert two new
worksheets, name them First and Last, and place all the month sheets
between First and Last. Then...
B2, copied across and down:
=SUMPRODUCT(--(THREED(First:Last!$F$12:$F$62)=$A2),--(THREED(First:Last!$G$2:$G$62)=B$1))
Billy Dixson wrote:
I have been trying to figure this out for some time and cannot seem to
get it to work. I am using Excel 2003
I need to know the number of times a certain situation occurs across
12 worksheets.
Workbook has 12 sheets named Jan, Feb, Mar, etc to Dec
All 12 sheets exactly the same (different data)
In column F12 to F62 is a range of numbers like 1010, 1056, 1168. All
are four digits and there may or may not be repetitions of the
numbers. The numbers could be any number from 1000 to 1200 (total of
200 possible numbers)
In column G12 to G62 there are 5 different names. Bill, Bob, Mike,
Helen and Sally that may or may not be repeated or not entered.
On a separate sheet I need to figure out the following.
In rows I want to put all the numbers.
In columns I want to put the names.
What I want to do is to count how many times Bill gave away item #
1056, 1137 etc. So that in the column below Bill beside each number
it will give me a total of instances Bill gave that item away in 12
months or a 0 if none of any number. It would be nice if it auto
updated when Bill gives away item # 1423 next month.
I want to do the same for all five people.
I have visited many web sites and tried numerous formulas but just
cannot get anything that works.
Can someone help me please.