[mso] Re: Excel - List formula

Hope someone can help on this little problem, I feel that I am almost there
but just cannot get it right.

The datalist consist of
Column A: Invoice Date, with dynamic range name "Invoice"
Column B: Invoice Amount, with dynamic range name "Amount"
Column C: Invoice Number, for info only
Column d: Invoice Description, for info only

On the same sheet, I am trying to consolidate the monthly invoice data using
formula.

Column H-J headers, Period, Count of Invoices, Invoice Totals 
Column H has the list of date formatted as MMM-YY format (Jan-05, Feb-05
etc), but is actually 01-Jan-05, 01-Feb-05 etc


Column I, I am trying to count the varying number of invoices per month.
Matching Year(Invoice) = Year(Period) and Month(Invoice) = Month(Period)

=COUNT(IF((YEAR(Invoice)=YEAR(Period))+
MONTH(Invoice)=MONTH(Period)),Invoice))

The formula above entered as a normal formula returns the total count of
invoices in the list.
When entered as an Array, it returns a number which does not seem to relate
to anything.

I have also tried the following:
=SUMPRODUCT((YEAR(Invoice) = YEAR(Period))+ (MONTH(Invoice)=MONTH(Period)))
which returns a #N/A, whether entered as an array or normal formula.

I have looked at the SumProduct topic at XLDynamic.com and cannot fathom the
formula out.  Any assistance would be most welcome.


Regards

Norman Pearce 
IT Training Manager
Ealing Hospital



*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: