Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Countif
Good morning, nothing can ever be easy in the Army. ;-)
I have a workbook with multiple worksheets. The First sheet contains totals etc. I want to be able to count all occurences of data in different tabs and return those values to specific locations on the first sheet. Example. I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific mission numbers that I need to track for each month. I want to track,for example, how many times each of them acquires a bolt and how many times each acquires a nut for each month. So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook. On the main Sheet I have each of the Soldiers names in Column A and I want to tally the number of bots and nuts for each month in the columns to the right. I have tried multiple times to do a countif, but I can't get it to work right. I can provide a sample of the workook if necessary. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Countif
Hi
Or even better, create a Pivod table based on this consolidated table. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Don Guillett" wrote in message ... I might put all on ONE sheet starting at row 5 row 5 datenameitem row 6 1/1/2007 jones bolt in row1 month number jones bolt in row 2 =sumproduct((month(a6:a600-1)*(b6:b600=b1)*(c6:c600=c1)) or filter the list and use SUBTOTAL -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... Good morning, nothing can ever be easy in the Army. ;-) I have a workbook with multiple worksheets. The First sheet contains totals etc. I want to be able to count all occurences of data in different tabs and return those values to specific locations on the first sheet. Example. I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific mission numbers that I need to track for each month. I want to track,for example, how many times each of them acquires a bolt and how many times each acquires a nut for each month. So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook. On the main Sheet I have each of the Soldiers names in Column A and I want to tally the number of bots and nuts for each month in the columns to the right. I have tried multiple times to do a countif, but I can't get it to work right. I can provide a sample of the workook if necessary. Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Countif
How about if I sent you a sample? I can't really 'change' the layout, it is
very involved and contains a lot of data. It is really alot of data and I am trying to make it so that it will automatically do some tallying to avoid errors. "Don Guillett" wrote: I might put all on ONE sheet starting at row 5 row 5 datenameitem row 6 1/1/2007 jones bolt in row1 month number jones bolt in row 2 =sumproduct((month(a6:a600-1)*(b6:b600=b1)*(c6:c600=c1)) or filter the list and use SUBTOTAL -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... Good morning, nothing can ever be easy in the Army. ;-) I have a workbook with multiple worksheets. The First sheet contains totals etc. I want to be able to count all occurences of data in different tabs and return those values to specific locations on the first sheet. Example. I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific mission numbers that I need to track for each month. I want to track,for example, how many times each of them acquires a bolt and how many times each acquires a nut for each month. So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook. On the main Sheet I have each of the Soldiers names in Column A and I want to tally the number of bots and nuts for each month in the columns to the right. I have tried multiple times to do a countif, but I can't get it to work right. I can provide a sample of the workook if necessary. Any help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Countif
I'm an old ex USAF personnel officer. Send to my address below
-- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... How about if I sent you a sample? I can't really 'change' the layout, it is very involved and contains a lot of data. It is really alot of data and I am trying to make it so that it will automatically do some tallying to avoid errors. "Don Guillett" wrote: I might put all on ONE sheet starting at row 5 row 5 datenameitem row 6 1/1/2007 jones bolt in row1 month number jones bolt in row 2 =sumproduct((month(a6:a600-1)*(b6:b600=b1)*(c6:c600=c1)) or filter the list and use SUBTOTAL -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... Good morning, nothing can ever be easy in the Army. ;-) I have a workbook with multiple worksheets. The First sheet contains totals etc. I want to be able to count all occurences of data in different tabs and return those values to specific locations on the first sheet. Example. I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific mission numbers that I need to track for each month. I want to track,for example, how many times each of them acquires a bolt and how many times each acquires a nut for each month. So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook. On the main Sheet I have each of the Soldiers names in Column A and I want to tally the number of bots and nuts for each month in the columns to the right. I have tried multiple times to do a countif, but I can't get it to work right. I can provide a sample of the workook if necessary. Any help would be greatly appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Countif
I sent it, should be there all set to confuse you! lol
"Don Guillett" wrote: I'm an old ex USAF personnel officer. Send to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... How about if I sent you a sample? I can't really 'change' the layout, it is very involved and contains a lot of data. It is really alot of data and I am trying to make it so that it will automatically do some tallying to avoid errors. "Don Guillett" wrote: I might put all on ONE sheet starting at row 5 row 5 datenameitem row 6 1/1/2007 jones bolt in row1 month number jones bolt in row 2 =sumproduct((month(a6:a600-1)*(b6:b600=b1)*(c6:c600=c1)) or filter the list and use SUBTOTAL -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... Good morning, nothing can ever be easy in the Army. ;-) I have a workbook with multiple worksheets. The First sheet contains totals etc. I want to be able to count all occurences of data in different tabs and return those values to specific locations on the first sheet. Example. I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific mission numbers that I need to track for each month. I want to track,for example, how many times each of them acquires a bolt and how many times each acquires a nut for each month. So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook. On the main Sheet I have each of the Soldiers names in Column A and I want to tally the number of bots and nuts for each month in the columns to the right. I have tried multiple times to do a countif, but I can't get it to work right. I can provide a sample of the workook if necessary. Any help would be greatly appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Countif
=IF(LEN($A4)<2,"",SUMPRODUCT(--('PRODUCTION BY ARCC
OCT07'!$B$2:$B$100=$A4),--('PRODUCTION BY ARCC OCT07'!F$2:F$100="x"))+SUMPRODUCT(--('PRODUCTION BY ARCC OCT07'!$K$2:$K$100=$A4),--('PRODUCTION BY ARCC OCT07'!O$2:O$100="x"))) -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... I sent it, should be there all set to confuse you! lol "Don Guillett" wrote: I'm an old ex USAF personnel officer. Send to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... How about if I sent you a sample? I can't really 'change' the layout, it is very involved and contains a lot of data. It is really alot of data and I am trying to make it so that it will automatically do some tallying to avoid errors. "Don Guillett" wrote: I might put all on ONE sheet starting at row 5 row 5 datenameitem row 6 1/1/2007 jones bolt in row1 month number jones bolt in row 2 =sumproduct((month(a6:a600-1)*(b6:b600=b1)*(c6:c600=c1)) or filter the list and use SUBTOTAL -- Don Guillett Microsoft MVP Excel SalesAid Software "SFCWoods" wrote in message ... Good morning, nothing can ever be easy in the Army. ;-) I have a workbook with multiple worksheets. The First sheet contains totals etc. I want to be able to count all occurences of data in different tabs and return those values to specific locations on the first sheet. Example. I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific mission numbers that I need to track for each month. I want to track,for example, how many times each of them acquires a bolt and how many times each acquires a nut for each month. So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook. On the main Sheet I have each of the Soldiers names in Column A and I want to tally the number of bots and nuts for each month in the columns to the right. I have tried multiple times to do a countif, but I can't get it to work right. I can provide a sample of the workook if necessary. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...) | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Advanced use of IF | Excel Worksheet Functions | |||
Advanced COUNTIF Function | Excel Worksheet Functions | |||
Excel 2000 - Advanced IF AND ELSE COUNTIF?? | Excel Worksheet Functions |