| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count across worksheets with two criteria
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count across worksheets with two criteria
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count across worksheets with two criteria
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count across worksheets with two criteria
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count across worksheets with two criteria
Not particularly exciting, but...
=SUMPRODUCT(--(N(OFFSET(INDIRECT("'"&SheetList&"'!F12:F62"), ROW(INDIRECT("12:62"))-12,0,1))=$A2), --(T(OFFSET(INDIRECT("'"&SheetList&"'!G12:G62"), ROW(INDIRECT("12:62"))-12,0,1))=B$1)) where SheetList needs to be a horizontal range. Toppers wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can i.. do a COUNT through mutiple worksheets? | Excel Discussion (Misc queries) | |||
| how do I count the numbers of row that meet 2 criteria | Excel Worksheet Functions | |||
| Count of Worksheets | Excel Discussion (Misc queries) | |||
| How to count # of worksheets? | Excel Discussion (Misc queries) | |||
| Count Unique Names in list w/ Additional Criteria? | Excel Worksheet Functions | |||
Linear Mode
