| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Your help please in updating a column using Count or an Array Formula
I am using Windows Vista Ultimate OS, Microsoft Office Ultimate-Excel 2007. Problem is updating, Sheet four (4) the tally sheet, the column total number of pieces received on a given day; along with the name of the departments receiving mail, the person in the department who is receiving the mail and the type of mailed received by the department. In one work book, I have (on Sheet 1), five (5) columns from A1:E50, named: Mail Process Date (date will constantly change), Mail From, Address To (potentially 37 persons with additions), Department (16 departments) and Mail Classified (11 types of mail). What has worked are drop down lists, named ranges, the =COUNTIF ($E$2:$E$41,"C_Not Opened") and the =SUMPRODUCT (--(DeptPic="ACCT"),--(MailPic="C_Not Opened")) formula. I have drop down lists (Sheet 2) for columns C1:E1; the Address To, Department and Mail Classified columns. In columns G1:H12, I have Mail Type Count and Count Mail Type. I am using =COUNTIF ($E$2:$E$41,"C_Not Opened"), which appears to correctly count the mail types by department. Okay so far, until I asked, can I have the mail classification counted by Departments and the total number of pieces received on a given day? I could not answer this question with combinations of Count, CountA, Countif or Sumif: I tried: count( D2:D50=ADM, if (count, e2:e50,=C_Not Opened)); CountA( D2:D50=ADM,if(countA, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if (countif,e2:e50,=C_Not Opened)); count( D2:D50=ADM, if (countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,if(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if(count,e2:e50,=C_Not Opened)); count( D2:D50=ADM, sumif(countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,sumif(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM,sum if (count,e2:e50,=C_Not Opened)); I searched this group, on this question, and the answers given by its members [--Members that are nothing short of being outstanding, professional, understanding and capable of advising, able to mentorship from afar, unselfishly teaching and sharing their time and experiences with Excel--] then produce Sheet 4, using =SUMPRODUCT(-- (DeptPic="ACCT"),--(MailPic="C_Not Opened").) in addition to establishing drop down lists and the Name Ranges for the various Mail Classification columns. I now have each department on a separate row and each mail classification in a separate column. The date will change and the senders will change. I had thought that I could simply enter the new information and this would update the sheet 4. I attempted to do this by placing sample information in the Mail Process Date (column A), Mail From (column B), via cut and paste. The totals did not change, even after I extended or shorten rows or changed the classification of the mail received, or the department receiving the mail. Is there an array formula or link that I should be using to update Sheet 4? [Moderator--You have my permission to edit, delete or to clarifying my request for help that allows such a request to be entered into this Group, --gk] Sheet 1 Mail Process Date Mail From Address To Department Mail Classified 02-Jan-08 Wellpoint Alfredo FRP C_Not Opened 02-Jan-08 Professional Ana EX Departmental Material 02-Jan-08 Huntington Andrea AR Domestic Return Receipt 02-Jan-08 David Anthony WHSE Instructed_Not To Open 02-Jan-08 Susan Arisela PD Invoice 02-Jan-08 Wellpoint Carl FRP Notice 02-Jan-08 Professional Clarissa FRP Opened_But Not Read 02-Jan-08 Wellpoint Czarina ACCT P&C_Not Opened 02-Jan-08 Professional Darren WHSE Postal Return 02-Jan-08 Huntington Elizabeth PD Received_As Is_ MailTypeCount CountMailType 5 C_Not Opened 6 Departmental Material 4 Domestic Return Receipt 4 Instructed_Not To Open 4 Invoice 5 Notice 3 Opened_But Not Read 2 P&C_Not Opened 3 Postal Return 3 Received_As Is_ 39 Total Mail Sheet 2 C_Not Opened Department Material Domestic Return Receipt Instructed_Not To Open Invoice Monthly Report Notice Opened_But Not Read P&C_Not Opened Postal Return Received_As Is_ Sheet 4 Instructed Opened C_ Domestic Not But P&C_ Received_ Not Dept Return To Not Not Postal As DeptList Opened Material Receipt Open Invoice Notice Read Opened Return Is_ TOTAL ACCT 0 0 1 1 0 0 0 1 0 0 3 ADM 4 6 0 0 0 3 1 0 0 0 14 AR 0 0 1 1 0 0 1 0 0 0 3 COM 0 0 0 0 0 0 0 0 0 0 0 CSFP 0 1 0 0 1 0 0 0 0 0 2 EX 0 1 0 0 0 1 0 1 0 0 3 FRP 1 0 0 0 0 1 1 0 0 0 3 HR 1 0 0 0 0 0 0 0 0 0 1 IOC 1 0 1 0 0 0 0 0 1 1 4 IT 0 0 0 0 1 0 0 0 0 1 2 NEKP 0 1 0 0 0 1 0 0 0 0 2 PD 0 0 0 0 1 0 0 1 0 1 3 PDEV 0 0 0 0 0 0 0 0 1 0 1 VOL 0 0 0 0 0 0 0 0 0 0 0 WHSE 0 0 1 2 0 0 0 0 1 0 4 Total 7 9 4 4 3 6 3 3 3 3 45 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Your help please in updating a column using Count or an ArrayFormula
I'm not sure what your DeptPic and MailPic names refer to, so I'm not
100% sure how to solve your problem. However, you might find this helpful. First of all, whenever you use SUMIF or SUMPRODUCT formulas, you must make sure that the values you're using match exactly each time. In other words if you use one spelling for your mail type, always stick with it. Once you've done that (I'm assuming you have it so far since your other formulas are working), try this. In the workbook scope, create names for your columns, using OFFSET range formulas to ensure you always include all the rows. Your definition for these would be along the lines of this: Name: MailProcessDate Refers to: =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1) In the above formula, the third argument (0) increases by 1 for each column, and the column reference in COUNTA changes accordingly. In other words, here's the range for the second column: Name: MailFrom Refers to: =OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$B:$B)-1,1) And so on. Assuming you follow this naming convention, for your SUMPRODUCT formulas on 'Sheet 4', you can use this kind of expression (assuming it starts in row 1 and column A): B2: =SUMPRODUCT(--Department=$A1, --MailClassified=B$1) If this doesn't work, then either your row names (department) or column name (mail classification) aren't spelled correctly. If it does work correctly, then you can copy this into B2:K16, and use SUM() function for row and line totals. Notice, you can also use a PivotTable for this kind of data. Since you're in Excel 2007, you can convert your 'Sheet 1' list to a table, then use it as source. Remember to refresh the pivot table each time you want the summary. Use Department as your rows, Mail Classified as your columns, and any other field in the data area for a count (Mail From, Address To, etc). Hopefully that helps. wrote: I am using Windows Vista Ultimate OS, Microsoft Office Ultimate-Excel 2007. Problem is updating, Sheet four (4) the tally sheet, the column total number of pieces received on a given day; along with the name of the departments receiving mail, the person in the department who is receiving the mail and the type of mailed received by the department. In one work book, I have (on Sheet 1), five (5) columns from A1:E50, named: Mail Process Date (date will constantly change), Mail From, Address To (potentially 37 persons with additions), Department (16 departments) and Mail Classified (11 types of mail). What has worked are drop down lists, named ranges, the =COUNTIF ($E$2:$E$41,"C_Not Opened") and the =SUMPRODUCT (--(DeptPic="ACCT"),--(MailPic="C_Not Opened")) formula. I have drop down lists (Sheet 2) for columns C1:E1; the Address To, Department and Mail Classified columns. In columns G1:H12, I have Mail Type Count and Count Mail Type. I am using =COUNTIF ($E$2:$E$41,"C_Not Opened"), which appears to correctly count the mail types by department. Okay so far, until I asked, can I have the mail classification counted by Departments and the total number of pieces received on a given day? I could not answer this question with combinations of Count, CountA, Countif or Sumif: I tried: count( D2:D50=ADM, if (count, e2:e50,=C_Not Opened)); CountA( D2:D50=ADM,if(countA, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if (countif,e2:e50,=C_Not Opened)); count( D2:D50=ADM, if (countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,if(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if(count,e2:e50,=C_Not Opened)); count( D2:D50=ADM, sumif(countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,sumif(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM,sum if (count,e2:e50,=C_Not Opened)); I searched this group, on this question, and the answers given by its members [--Members that are nothing short of being outstanding, professional, understanding and capable of advising, able to mentorship from afar, unselfishly teaching and sharing their time and experiences with Excel--] then produce Sheet 4, using =SUMPRODUCT(-- (DeptPic="ACCT"),--(MailPic="C_Not Opened").) in addition to establishing drop down lists and the Name Ranges for the various Mail Classification columns. I now have each department on a separate row and each mail classification in a separate column. The date will change and the senders will change. I had thought that I could simply enter the new information and this would update the sheet 4. I attempted to do this by placing sample information in the Mail Process Date (column A), Mail From (column B), via cut and paste. The totals did not change, even after I extended or shorten rows or changed the classification of the mail received, or the department receiving the mail. Is there an array formula or link that I should be using to update Sheet 4? [Moderator--You have my permission to edit, delete or to clarifying my request for help that allows such a request to be entered into this Group, --gk] Sheet 1 Mail Process Date Mail From Address To Department Mail Classified 02-Jan-08 Wellpoint Alfredo FRP C_Not Opened 02-Jan-08 Professional Ana EX Departmental Material 02-Jan-08 Huntington Andrea AR Domestic Return Receipt 02-Jan-08 David Anthony WHSE Instructed_Not To Open 02-Jan-08 Susan Arisela PD Invoice 02-Jan-08 Wellpoint Carl FRP Notice 02-Jan-08 Professional Clarissa FRP Opened_But Not Read 02-Jan-08 Wellpoint Czarina ACCT P&C_Not Opened 02-Jan-08 Professional Darren WHSE Postal Return 02-Jan-08 Huntington Elizabeth PD Received_As Is_ MailTypeCount CountMailType 5 C_Not Opened 6 Departmental Material 4 Domestic Return Receipt 4 Instructed_Not To Open 4 Invoice 5 Notice 3 Opened_But Not Read 2 P&C_Not Opened 3 Postal Return 3 Received_As Is_ 39 Total Mail Sheet 2 C_Not Opened Department Material Domestic Return Receipt Instructed_Not To Open Invoice Monthly Report Notice Opened_But Not Read P&C_Not Opened Postal Return Received_As Is_ Sheet 4 Instructed Opened C_ Domestic Not But P&C_ Received_ Not Dept Return To Not Not Postal As DeptList Opened Material Receipt Open Invoice Notice Read Opened Return Is_ TOTAL ACCT 0 0 1 1 0 0 0 1 0 0 3 ADM 4 6 0 0 0 3 1 0 0 0 14 AR 0 0 1 1 0 0 1 0 0 0 3 COM 0 0 0 0 0 0 0 0 0 0 0 CSFP 0 1 0 0 1 0 0 0 0 0 2 EX 0 1 0 0 0 1 0 1 0 0 3 FRP 1 0 0 0 0 1 1 0 0 0 3 HR 1 0 0 0 0 0 0 0 0 0 1 IOC 1 0 1 0 0 0 0 0 1 1 4 IT 0 0 0 0 1 0 0 0 0 1 2 NEKP 0 1 0 0 0 1 0 0 0 0 2 PD 0 0 0 0 1 0 0 1 0 1 3 PDEV 0 0 0 0 0 0 0 0 1 0 1 VOL 0 0 0 0 0 0 0 0 0 0 0 WHSE 0 0 1 2 0 0 0 0 1 0 4 Total 7 9 4 4 3 6 3 3 3 3 45 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Your help please in updating a column using Count or an ArrayFormula
Two things in addition to all that. If you do use Excel 2007's Table
feature, you can refer to columns by name without the OFFSET named ranges. So, for example if your table is called MailData (you actually have to change this in Table-Design-Properties section of the ribbon, when within the table), then the following is a valid reference: =MailData[[#Data],[Mail Classified]] And you can use this in your 'Sheet 4' SUMPRODUCTS (see above post), such as like so: B2: =SUMPRODUCT(--MailData[[#Data],[Mail Classified]]=B$1,-- MailData[[#Data],[Department]]=$A1) Secondly, if you want to keep this compatible with earlier versions, and use PivotTables, define a name for the pivot data like so: Name: MailData Refers To: =OFFSET('Sheet 1'!$A$1,0,0,COUNTA('Sheet1'!$A: $A),COUNTA('Sheet 1'!$1:$1)) This way the range will expand dynamically as you add data to 'Sheet 1', and won't utilize the non-compatible Tables feature. Also, for compatibility purposes, ensure that you save your workbook in compatibility mode (close and reopen it) before you create the PivotTable. On Feb 10, 11:12*pm, ilia wrote: I'm not sure what your DeptPic and MailPic names refer to, so I'm not 100% sure how to solve your problem. *However, you might find this helpful. First of all, whenever you use SUMIF or SUMPRODUCT formulas, you must make sure that the values you're using match exactly each time. *In other words if you use one spelling for your mail type, always stick with it. Once you've done that (I'm assuming you have it so far since your other formulas are working), try this. *In the workbook scope, create names for your columns, using OFFSET range formulas to ensure you always include all the rows. *Your definition for these would be along the lines of this: Name: MailProcessDate Refers to: =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1) In the above formula, the third argument (0) increases by 1 for each column, and the column reference in COUNTA changes accordingly. *In other words, here's the range for the second column: Name: MailFrom Refers to: =OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$B:$B)-1,1) And so on. *Assuming you follow this naming convention, for your SUMPRODUCT formulas on 'Sheet 4', you can use this kind of expression (assuming it starts in row 1 and column A): B2: =SUMPRODUCT(--Department=$A1, --MailClassified=B$1) If this doesn't work, then either your row names (department) or column name (mail classification) aren't spelled correctly. *If it does work correctly, then you can copy this into B2:K16, and use SUM() function for row and line totals. Notice, you can also use a PivotTable for this kind of data. *Since you're in Excel 2007, you can convert your 'Sheet 1' list to a table, then use it as source. *Remember to refresh the pivot table each time you want the summary. *Use Department as your rows, Mail Classified as your columns, and any other field in the data area for a count (Mail From, Address To, etc). Hopefully that helps. wrote: I am using Windows Vista Ultimate OS, Microsoft Office Ultimate-Excel 2007. Problem is updating, Sheet four (4) the tally sheet, the column total number of pieces received on a given day; along with the name of the departments receiving mail, the person in the department who is receiving the mail and the type of mailed received by the department. In one work book, I have (on Sheet 1), five (5) columns from A1:E50, named: Mail Process Date (date will constantly change), Mail From, Address To (potentially 37 persons with additions), Department (16 departments) and Mail Classified (11 types of mail). What has worked are drop down lists, named ranges, the =COUNTIF ($E$2:$E$41,"C_Not Opened") and the =SUMPRODUCT (--(DeptPic="ACCT"),--(MailPic="C_Not Opened")) formula. I have drop down lists (Sheet 2) for columns C1:E1; the Address To, Department and Mail Classified columns. In columns G1:H12, I have Mail Type Count and Count Mail Type. I am using =COUNTIF ($E$2:$E$41,"C_Not Opened"), which appears to correctly count the mail types by department. Okay so far, until I asked, can I have the mail classification counted by Departments and the total number of pieces received on a given day? I could not answer this question with combinations of Count, CountA, Countif or Sumif: I tried: count( D2:D50=ADM, if (count, e2:e50,=C_Not Opened)); CountA( D2:D50=ADM,if(countA, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if (countif,e2:e50,=C_Not Opened)); count( D2:D50=ADM, if (countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,if(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if(count,e2:e50,=C_Not Opened)); count( D2:D50=ADM, sumif(countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,sumif(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM,sum if (count,e2:e50,=C_Not Opened)); I *searched this group, on this question, and the answers given by its members [--Members that are nothing short of being outstanding, professional, understanding and capable of advising, able to mentorship from afar, unselfishly teaching and sharing their time and experiences with Excel--] then produce Sheet 4, using =SUMPRODUCT(-- (DeptPic="ACCT"),--(MailPic="C_Not Opened").) in addition to establishing drop down lists and the Name Ranges for the various Mail Classification columns. I now have each department on a separate row and each mail classification in a separate column. The date will change and the senders will change. I had thought that I could simply enter the new information and this would update the sheet 4. *I attempted to do this by placing sample information in the Mail Process Date (column A), Mail From (column B), via cut and paste. The totals did not change, even after I extended or shorten rows or changed the classification of the mail received, or the department receiving the mail. Is there an array formula or link that I should be using to update Sheet 4? [Moderator--You have my permission to edit, delete or to clarifying my request for help that allows such a request to be entered into this Group, --gk] Sheet 1 Mail Process Date *Mail From * * * Address To * * *Department * * *Mail Classified 02-Jan-08 *Wellpoint * * * Alfredo FRP * * C_Not Opened 02-Jan-08 *Professional * *Ana * * EX * * *Departmental Material 02-Jan-08 *Huntington * * *Andrea *AR * * *Domestic Return Receipt 02-Jan-08 *David * Anthony WHSE * *Instructed_Not To Open 02-Jan-08 *Susan * Arisela PD * * *Invoice 02-Jan-08 *Wellpoint * * * Carl * *FRP * * Notice 02-Jan-08 *Professional * *Clarissa * * * *FRP * * Opened_But Not Read 02-Jan-08 *Wellpoint * * * Czarina ACCT * *P&C_Not Opened 02-Jan-08 *Professional * *Darren *WHSE * *Postal Return 02-Jan-08 *Huntington * * *Elizabeth * * * PD * * *Received_As Is_ MailTypeCount * * *CountMailType 5 *C_Not Opened 6 *Departmental Material 4 *Domestic Return Receipt 4 *Instructed_Not To Open 4 *Invoice 5 *Notice 3 *Opened_But Not Read 2 *P&C_Not Opened 3 *Postal Return 3 *Received_As Is_ 39 Total Mail Sheet 2 C_Not Opened Department Material Domestic Return Receipt Instructed_Not To Open Invoice Monthly Report Notice Opened_But Not Read P&C_Not Opened Postal Return Received_As Is_ Sheet 4 * * * * * * * * * * * * * *Instructed * * * * * * * * * * *Opened * *C_ * * * * * * *Domestic * * * *Not * * * * * * * * * * But * * P&C_ * * * * * * * *Received_ * *Not * * Dept * *Return *To * * * * * * * * * * *Not * * Not * * Postal *As DeptList * Opened *Material * * * *Receipt Open * *Invoice Notice *Read * *Opened Return * * Is_ * * TOTAL ACCT * * * 0 * * * 0 * * * 1 * * * 1 * * * 0 * * * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 3 ADM * * * *4 * * * 6 * * * 0 * * * 0 * * * 0 * * * 3 * * * 1 * * * 0 * * * 0 * * * 0 * * * 14 AR 0 * * * 0 * * * 1 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * 3 COM * * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 CSFP * * * 0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 2 EX 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * 1 * * * 0 * * * 1 * * * 0 * * * 0 * * * 3 FRP * * * *1 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * 1 * * * 0 * * * 0 * * * 0 * * * 3 HR 1 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 IOC * * * *1 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * 1 * * * 4 IT 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * 2 NEKP * * * 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * 0 * * * 2 PD 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0 * * * 1 * * * 3 PDEV * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * 0 * * * 1 VOL * * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 WHSE * * * 0 * * * 0 * * * 1 * * * 2 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * 0 * * * 4 Total * * *7 * * * 9 * * * 4 * * * 4 * * * 3 * * * 6 * * * 3 * * * 3 * * * 3 * * * 3 * * * 45 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Your help please in updating a column using Count or an ArrayFormula
On Feb 10, 8:24 pm, ilia wrote:
Two things in addition to all that. If you do use Excel 2007's Table feature, you can refer to columns by name without the OFFSET named ranges. So, for example if your table is called MailData (you actually have to change this in Table-Design-Properties section of the ribbon, when within the table), then the following is a valid reference: =MailData[[#Data],[Mail Classified]] And you can use this in your 'Sheet 4' SUMPRODUCTS (see above post), such as like so: B2: =SUMPRODUCT(--MailData[[#Data],[Mail Classified]]=B$1,-- MailData[[#Data],[Department]]=$A1) Secondly, if you want to keep this compatible with earlier versions, and use PivotTables, define a name for the pivot data like so: Name: MailData Refers To: =OFFSET('Sheet 1'!$A$1,0,0,COUNTA('Sheet1'!$A: $A),COUNTA('Sheet 1'!$1:$1)) This way the range will expand dynamically as you add data to 'Sheet 1', and won't utilize the non-compatible Tables feature. Also, for compatibility purposes, ensure that you save your workbook in compatibility mode (close and reopen it) before you create the PivotTable. On Feb 10, 11:12 pm, ilia wrote: I'm not sure what your DeptPic and MailPic names refer to, so I'm not 100% sure how to solve your problem. However, you might find this helpful. First of all, whenever you use SUMIF or SUMPRODUCT formulas, you must make sure that the values you're using match exactly each time. In other words if you use one spelling for your mail type, always stick with it. Once you've done that (I'm assuming you have it so far since your other formulas are working), try this. In the workbook scope, create names for your columns, using OFFSET range formulas to ensure you always include all the rows. Your definition for these would be along the lines of this: Name: MailProcessDate Refers to: =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1) In the above formula, the third argument (0) increases by 1 for each column, and the column reference in COUNTA changes accordingly. In other words, here's the range for the second column: Name: MailFrom Refers to: =OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$B:$B)-1,1) And so on. Assuming you follow this naming convention, for your SUMPRODUCT formulas on 'Sheet 4', you can use this kind of expression (assuming it starts in row 1 and column A): B2: =SUMPRODUCT(--Department=$A1, --MailClassified=B$1) If this doesn't work, then either your row names (department) or column name (mail classification) aren't spelled correctly. If it does work correctly, then you can copy this into B2:K16, and use SUM() function for row and line totals. Notice, you can also use a PivotTable for this kind of data. Since you're in Excel 2007, you can convert your 'Sheet 1' list to a table, then use it as source. Remember to refresh the pivot table each time you want the summary. Use Department as your rows, Mail Classified as your columns, and any other field in the data area for a count (Mail From, Address To, etc). Hopefully that helps. wrote: I am using Windows Vista Ultimate OS, Microsoft Office Ultimate-Excel 2007. Problem is updating, Sheet four (4) the tally sheet, the column total number of pieces received on a given day; along with the name of the departments receiving mail, the person in the department who is receiving the mail and the type of mailed received by the department. In one work book, I have (on Sheet 1), five (5) columns from A1:E50, named: Mail Process Date (date will constantly change), Mail From, Address To (potentially 37 persons with additions), Department (16 departments) and Mail Classified (11 types of mail). What has worked are drop down lists, named ranges, the =COUNTIF ($E$2:$E$41,"C_Not Opened") and the =SUMPRODUCT (--(DeptPic="ACCT"),--(MailPic="C_Not Opened")) formula. I have drop down lists (Sheet 2) for columns C1:E1; the Address To, Department and Mail Classified columns. In columns G1:H12, I have Mail Type Count and Count Mail Type. I am using =COUNTIF ($E$2:$E$41,"C_Not Opened"), which appears to correctly count the mail types by department. Okay so far, until I asked, can I have the mail classification counted by Departments and the total number of pieces received on a given day? I could not answer this question with combinations of Count, CountA, Countif or Sumif: I tried: count( D2:D50=ADM, if (count, e2:e50,=C_Not Opened)); CountA( D2:D50=ADM,if(countA, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if (countif,e2:e50,=C_Not Opened)); count( D2:D50=ADM, if (countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,if(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM, if(count,e2:e50,=C_Not Opened)); count( D2:D50=ADM, sumif(countA, e2:e50,=C_Not Opened)); countA( D2:D50=ADM,sumif(countif, e2:e50,=C_Not Opened)); Countif( D2:D50=ADM,sum if (count,e2:e50,=C_Not Opened)); I searched this group, on this question, and the answers given by its members [--Members that are nothing short of being outstanding, professional, understanding and capable of advising, able to mentorship from afar, unselfishly teaching and sharing their time and experiences with Excel--] then produce Sheet 4, using =SUMPRODUCT(-- (DeptPic="ACCT"),--(MailPic="C_Not Opened").) in addition to establishing drop down lists and the Name Ranges for the various Mail Classification columns. I now have each department on a separate row and each mail classification in a separate column. The date will change and the senders will change. I had thought that I could simply enter the new information and this would update the sheet 4. I attempted to do this by placing sample information in the Mail Process Date (column A), Mail From (column B), via cut and paste. The totals did not change, even after I extended or shorten rows or changed the classification of the mail received, or the department receiving the mail. Is there an array formula or link that I should be using to update Sheet 4? [Moderator--You have my permission to edit, delete or to clarifying my request for help that allows such a request to be entered into this Group, --gk] Sheet 1 Mail Process Date Mail From Address To Department Mail Classified 02-Jan-08 Wellpoint Alfredo FRP C_Not Opened 02-Jan-08 Professional Ana EX Departmental Material 02-Jan-08 Huntington Andrea AR Domestic Return Receipt 02-Jan-08 David Anthony WHSE Instructed_Not To Open 02-Jan-08 Susan Arisela PD Invoice 02-Jan-08 Wellpoint Carl FRP Notice 02-Jan-08 Professional Clarissa FRP Opened_But Not Read 02-Jan-08 Wellpoint Czarina ACCT P&C_Not Opened 02-Jan-08 Professional Darren WHSE Postal Return 02-Jan-08 Huntington Elizabeth PD Received_As Is_ MailTypeCount CountMailType 5 C_Not Opened 6 Departmental Material 4 Domestic Return Receipt 4 Instructed_Not To Open 4 Invoice 5 Notice 3 Opened_But Not Read 2 P&C_Not Opened 3 Postal Return 3 Received_As Is_ 39 Total Mail Sheet 2 C_Not Opened Department Material Domestic Return Receipt Instructed_Not To Open Invoice Monthly Report Notice Opened_But Not Read P&C_Not Opened Postal Return Received_As Is_ Sheet 4 Instructed Opened C_ Domestic Not But P&C_ Received_ Not Dept Return To Not Not Postal As DeptList Opened Material Receipt Open Invoice Notice Read Opened Return Is_ TOTAL ACCT 0 0 1 1 0 0 0 1 0 0 3 ADM 4 6 0 0 0 3 1 0 0 0 14 AR 0 0 1 1 0 0 1 0 0 0 3 COM 0 0 0 0 0 0 0 0 0 0 0 CSFP 0 1 0 0 1 0 0 0 0 0 2 EX 0 1 0 0 0 1 0 1 0 0 3 FRP 1 0 0 0 0 1 1 0 0 0 3 HR 1 0 0 0 0 0 0 0 0 0 1 IOC 1 0 1 0 0 0 0 0 1 1 4 IT 0 0 0 0 1 0 0 0 0 1 2 NEKP 0 1 0 0 0 1 0 0 0 0 2 PD 0 0 0 0 1 0 0 1 0 1 3 PDEV 0 0 0 0 0 0 0 0 1 0 1 VOL 0 0 0 0 0 0 0 0 0 0 0 ... read more - Hide quoted text - - Show quoted text - Illa...Wow! Simply beyond words: Thank you for the insight; the Offset solution, the teaching, and the homework assignments. Thank you for a solution I would not have arrived at, on my own, thank you for sharing your time and experiences with me...Wow! George |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count(if(... using array formula: can I use a named range in my ca | Excel Worksheet Functions | |||
| Conditional Count (Array Formula?) | Excel Worksheet Functions | |||
| Count If Array Formula | Excel Worksheet Functions | |||
| How can you use count with an array formula similar to using sum | Excel Worksheet Functions | |||
| Formula to count number of dates in an array | Excel Worksheet Functions | |||
Linear Mode
