Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function for different array | Excel Worksheet Functions | |||
OR function in array-entered IF function | Excel Worksheet Functions | |||
AND in an array function? | Excel Worksheet Functions | |||
Array Function | Excel Worksheet Functions | |||
array function | Excel Discussion (Misc queries) |