Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
I am having trouble with the following formula:
=SUMPRODUCT(--(RBS!A2:A9999="1/4/2008"),--(RBS!A2:A9999<="31/3/2009"),--(RBS!M2:M9999="Directors Loan"),RBS!L2:L9999) It is giving me a "0" as the result. I know this is not the case. Column A is the date range Column M is the description of the item Column L is the cost for the item If you should have an answer to the above question please let me have it in the above format for ease of understanding. Many thanks in advance. Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
Mark,
I'd do it like this =SUMPRODUCT((RBS!A2:A9999=A1)*(RBS!A2:A9999<=B1)* (RBS!M2:M9999=C1)*(RBS!L2:L9999)) Where A1 & A2 are the date range and A3 is the text Directors choice Mike "Mark" wrote: I am having trouble with the following formula: =SUMPRODUCT(--(RBS!A2:A9999="1/4/2008"),--(RBS!A2:A9999<="31/3/2009"),--(RBS!M2:M9999="Directors Loan"),RBS!L2:L9999) It is giving me a "0" as the result. I know this is not the case. Column A is the date range Column M is the description of the item Column L is the cost for the item If you should have an answer to the above question please let me have it in the above format for ease of understanding. Many thanks in advance. Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
Mark,
You need to convert your date strings to dates: =SUMPRODUCT((RBS!A2:A9=DATEVALUE("1/4/2008"))*(RBS!A2:A9<=DATEVALUE("31/3/2009"))*(RBS!M2:M9="Directors Loan")*RBS!L2:L9) Or use cell references.... HTH, Bernie MS Excel MVP "Mark" wrote in message ... I am having trouble with the following formula: =SUMPRODUCT(--(RBS!A2:A9999="1/4/2008"),--(RBS!A2:A9999<="31/3/2009"),--(RBS!M2:M9999="Directors Loan"),RBS!L2:L9999) It is giving me a "0" as the result. I know this is not the case. Column A is the date range Column M is the description of the item Column L is the cost for the item If you should have an answer to the above question please let me have it in the above format for ease of understanding. Many thanks in advance. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|