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