Your help please in updating a column using Count or an Array Formula

R

Realitygdk

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
 
I

ilia

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.


[email protected] said:
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
Click to expand...
 
I

ilia

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.


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.

[email protected] said:
I am using Windows Vista Ultimate OS, Microsoft Office Ultimate-Excel
2007.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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));
Click to expand...
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.
Click to expand...
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.
Click to expand...
Is there an array formula or link that I should be using to update
Sheet 4?
Click to expand...
[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_
Click to expand...
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
Click to expand...
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_
Click to expand...
Sheet 4
Click to expand...
                           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
Click to expand...
Click to expand...
 
R

Realitygdk

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.

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.
Click to expand...
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.
Click to expand...
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:
Click to expand...
Name: MailProcessDate
Refers to: =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
Click to expand...
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:
Click to expand...
Name: MailFrom
Refers to: =OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$B:$B)-1,1)
Click to expand...
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):
Click to expand...
B2: =SUMPRODUCT(--Department=$A1, --MailClassified=B$1)
Click to expand...
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.
Click to expand...
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).
Click to expand...
Hopefully that helps.
Click to expand...
[email protected] said:
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
Click to expand...
Click to expand...

...

read more >>- Hide quoted text -

- Show quoted text -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can you use an INDEX array in a COUNTIF formula ? 1
Formula to count data in a column that falls between a range 3
need formula to count non-zero items in an array based on a vlooku 2
Continuation to equivalent for formula - to Mr. Harlan Grove (and notonly...) 1
sum of info in a range based on dates 4
OLD PROBLEM without SOLUTION , part || 1
Conditional Count Formula 2
Count text occurences in range when criteria in other column is me 3

Top