Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
Hi Everyone
I am trying to achieve the following Sheet 2 contains data imported from an Access Spreadsheet and I am trying to analyise this data on Sheet 1 Sheet1!A1 contains date field in mmm-yy format Sheet2!Column A is a date field in mmm-yy format and contains some 7500 rows growing at 200 per month Sheet2!Column B contains one of four string text fields Sheet2!Column C contains a numeric value I want to try and create a formula that will set Sheet1!B1 to the sum of Sheet2!Column C where a) Sheet2!Column A is the same as Sheet1A1 (i.e. the same month and year) and b) Sheet2!Column B is equal to "Text1" I will then simply replciate this formula across all four text string values and this will give me the sums I need for all four texts. I am having a real issue trying to resolve this - can anyone help please thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
=SUMPRODUCT(--(Sheet2!A1:A65535=Sheet1!A1),--(Sheet2!B1:B65535="AAA"),Sheet2!C1:C65535)
-- David Biddulph "Alexey" wrote in message ... Hi Everyone I am trying to achieve the following Sheet 2 contains data imported from an Access Spreadsheet and I am trying to analyise this data on Sheet 1 Sheet1!A1 contains date field in mmm-yy format Sheet2!Column A is a date field in mmm-yy format and contains some 7500 rows growing at 200 per month Sheet2!Column B contains one of four string text fields Sheet2!Column C contains a numeric value I want to try and create a formula that will set Sheet1!B1 to the sum of Sheet2!Column C where a) Sheet2!Column A is the same as Sheet1A1 (i.e. the same month and year) and b) Sheet2!Column B is equal to "Text1" I will then simply replciate this formula across all four text string values and this will give me the sums I need for all four texts. I am having a real issue trying to resolve this - can anyone help please thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
David, very many thanks - I dont think i would have ever got to that !
I am not sure what the two dashes are for between the arrays - i tried looking on Help but couldn't find anything yet I am still having a problem - I have replaced the syntax with the actual references but I am getting a 0 returned, when from the data there should be 18. Can you just confirm that what i have is OK: =SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535) Thanks A "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =SUMPRODUCT(--(Sheet2!A1:A65535=Sheet1!A1),--(Sheet2!B1:B65535="AAA"),Sheet2!C1:C65535) -- David Biddulph "Alexey" wrote in message ... Hi Everyone I am trying to achieve the following Sheet 2 contains data imported from an Access Spreadsheet and I am trying to analyise this data on Sheet 1 Sheet1!A1 contains date field in mmm-yy format Sheet2!Column A is a date field in mmm-yy format and contains some 7500 rows growing at 200 per month Sheet2!Column B contains one of four string text fields Sheet2!Column C contains a numeric value I want to try and create a formula that will set Sheet1!B1 to the sum of Sheet2!Column C where a) Sheet2!Column A is the same as Sheet1A1 (i.e. the same month and year) and b) Sheet2!Column B is equal to "Text1" I will then simply replciate this formula across all four text string values and this will give me the sums I need for all four texts. I am having a real issue trying to resolve this - can anyone help please thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
The two dashes are what is known as a double unary minus, and convert a TRUE
or FALSE to 1 or 0 so that you can do arithmetic such as the multiplication. Are you sure that the dates in your column M and in A5 are both actual dates, and that one of them isn't text? [Try reformatting temporarily and see whether the date format changes.] Are you sure that your column N hasn't got spurious spaces or other non-printing characters before or after the "Not Moderated"? Does =LEN(Nnn) give you the length of string you expect? 13 in this case? Are you sure that the contents of column P are numbers, and not text? If you're not getting the result you expect, break the formula down and look at each element individually. For example, if you think that row 99 satifies your conditions, look at what formulae such as the following give: ='Data from MIS'!M99=A5 ='Data from MIS'!N99="Not Moderated" -- David Biddulph "Alexey" wrote in message ... David, very many thanks - I dont think i would have ever got to that ! I am not sure what the two dashes are for between the arrays - i tried looking on Help but couldn't find anything yet I am still having a problem - I have replaced the syntax with the actual references but I am getting a 0 returned, when from the data there should be 18. Can you just confirm that what i have is OK: =SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535) Thanks A "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =SUMPRODUCT(--(Sheet2!A1:A65535=Sheet1!A1),--(Sheet2!B1:B65535="AAA"),Sheet2!C1:C65535) -- David Biddulph "Alexey" wrote in message ... Hi Everyone I am trying to achieve the following Sheet 2 contains data imported from an Access Spreadsheet and I am trying to analyise this data on Sheet 1 Sheet1!A1 contains date field in mmm-yy format Sheet2!Column A is a date field in mmm-yy format and contains some 7500 rows growing at 200 per month Sheet2!Column B contains one of four string text fields Sheet2!Column C contains a numeric value I want to try and create a formula that will set Sheet1!B1 to the sum of Sheet2!Column C where a) Sheet2!Column A is the same as Sheet1A1 (i.e. the same month and year) and b) Sheet2!Column B is equal to "Text1" I will then simply replciate this formula across all four text string values and this will give me the sums I need for all four texts. I am having a real issue trying to resolve this - can anyone help please thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can someone help with this complex formula? | Excel Worksheet Functions | |||
Need Help with complex formula | Excel Worksheet Functions | |||
Complex formula | Excel Discussion (Misc queries) | |||
Complex =mid formula | Excel Worksheet Functions | |||
complex formula | Excel Worksheet Functions |