Three way array function
 

Are these possible?
I've got the following formula to work out how many instances there are
of "Apples" and "COM" occuring at the same time

{=COUNT(IF((leasetracker!$E$2:$E$2000="Apples")*(l easetracker!$H$2:$H$2000="COM"),leasetracker!$A$2: $A$20000))}

I want to work out how many times they occur between two dates. (They
would be in column Z

Many Thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=545890

Three way array function
 

Sounds like SumProduct to me (a lot of postings on this

=sumproduct(--(leasetracker!$E$2:$E$2000="Apples"),--(leasetracker!$H$2:$H$2000="COM"),--(DateRange=firstdate),--(daterange<=seconddate))

will do it for you - obviously replace the daterange, firstdate and
seconddate with the relevent information - and it's no longer an array
function!

Three way array function
 

for the count over all the data:

=Sumproduct(--(leasetracker!$E$2:$E$2000="Apples"),--(leasetracker!$H$2:$H$2000="COM"))

If you want to consider dates


=Sumproduct(--(leasetracker!$E$2:$E$2000="Apples"),--(leasetracker!$H$2:$H$2000="COM"),--(leasetracker!$A$2:$A$2000=DateValue("01/01/2006")),--(leasetracker!$A$2:$A$2000<=DateValue("01/31/2006")))

Sumproduct does not have to be array entered.

--
Regards,
Tom Ogilvy



"M3Cobb" wrote:


Are these possible?
I've got the following formula to work out how many instances there are
of "Apples" and "COM" occuring at the same time

{=COUNT(IF((leasetracker!$E$2:$E$2000="Apples")*(l easetracker!$H$2:$H$2000="COM"),leasetracker!$A$2: $A$20000))}

I want to work out how many times they occur between two dates. (They
would be in column Z

Many Thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=545890