If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|