[mso] Re: Excel - List formula

Norman, 

I do something similar to track my own invoices. In cell I2, try this:

        =COUNTIF(Months,">"&I2)-COUNTIF(Months,">"&I3)

Ray 

------------------------------------
GR Business Process Solutions
Ray Blake
Head of Software Design
ray@xxxxxxxxx
Braedon
Newell Road
Hemel Hempstead
Herts HP3 9PD
tel: 01442 396518
fax: 01442 389353
mobile: 07834 226601
www.grbps.com
------------------------------------

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Pearce, Norman
Sent: 26 October 2005 14:25
To: 'mso@xxxxxxxxxxxxx'
Subject: [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: