Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM conditioned to range
Hi All
I need to produce a sum based on a accounts database. In column A I have accounts from 1000 to 9000 (named accsPL) In column B to M I have numbers. Each of these columns is one month in the year. The formula should sum all values where account number is greater than X and lower than Y and return the value in Column headed Z where X,Y and Z are specified in separate cells. I thought of: =SUM(IF((accsPLJ9)*(accsPL<K9),'BD PL'!D8:D12)) My problem is that I want the last part to be moveable as D8:D12 is month 3 and I want it to be the month I specify. Also, this database is a pivot table so it will grow in length every month when updated. Do you guys/girls have any idea on how to overcome this? Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM conditioned to range
Try this
=SUMPRODUCT((accsPLJ9)*(accsPL<K9)*INDEX(B8:M12,0 ,L9)) with the month number in L9 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Pedro AM" wrote in message ... Hi All I need to produce a sum based on a accounts database. In column A I have accounts from 1000 to 9000 (named accsPL) In column B to M I have numbers. Each of these columns is one month in the year. The formula should sum all values where account number is greater than X and lower than Y and return the value in Column headed Z where X,Y and Z are specified in separate cells. I thought of: =SUM(IF((accsPLJ9)*(accsPL<K9),'BD PL'!D8:D12)) My problem is that I want the last part to be moveable as D8:D12 is month 3 and I want it to be the month I specify. Also, this database is a pivot table so it will grow in length every month when updated. Do you guys/girls have any idea on how to overcome this? Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM conditioned to range
Hi Pidro,
you mean database of accounts where are all records are non-stagnant or non-blank. Meaning all are active and has monthly figures. Maybe i am just confused cause in my thinking even we have 0 value in record, that means there is a transaction involved. If cell record is blank, this means no transaction involved on that month for a specific account. Are you confident that there will be a transaction for the 1000-9000 acounts every month cause this has something to do with your requested formula ? "Pedro AM" wrote: Hi All I need to produce a sum based on a accounts database. In column A I have accounts from 1000 to 9000 (named accsPL) In column B to M I have numbers. Each of these columns is one month in the year. The formula should sum all values where account number is greater than X and lower than Y and return the value in Column headed Z where X,Y and Z are specified in separate cells. I thought of: =SUM(IF((accsPLJ9)*(accsPL<K9),'BD PL'!D8:D12)) My problem is that I want the last part to be moveable as D8:D12 is month 3 and I want it to be the month I specify. Also, this database is a pivot table so it will grow in length every month when updated. Do you guys/girls have any idea on how to overcome this? Thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM conditioned to range
Hi
yes, there will be transactions every month. Thanks for your help and also to Bob "driller" wrote: Hi Pidro, you mean database of accounts where are all records are non-stagnant or non-blank. Meaning all are active and has monthly figures. Maybe i am just confused cause in my thinking even we have 0 value in record, that means there is a transaction involved. If cell record is blank, this means no transaction involved on that month for a specific account. Are you confident that there will be a transaction for the 1000-9000 acounts every month cause this has something to do with your requested formula ? "Pedro AM" wrote: Hi All I need to produce a sum based on a accounts database. In column A I have accounts from 1000 to 9000 (named accsPL) In column B to M I have numbers. Each of these columns is one month in the year. The formula should sum all values where account number is greater than X and lower than Y and return the value in Column headed Z where X,Y and Z are specified in separate cells. I thought of: =SUM(IF((accsPLJ9)*(accsPL<K9),'BD PL'!D8:D12)) My problem is that I want the last part to be moveable as D8:D12 is month 3 and I want it to be the month I specify. Also, this database is a pivot table so it will grow in length every month when updated. Do you guys/girls have any idea on how to overcome this? Thank you |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM conditioned to range
Are you sorted?
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Pedro AM" wrote in message ... Hi yes, there will be transactions every month. Thanks for your help and also to Bob "driller" wrote: Hi Pidro, you mean database of accounts where are all records are non-stagnant or non-blank. Meaning all are active and has monthly figures. Maybe i am just confused cause in my thinking even we have 0 value in record, that means there is a transaction involved. If cell record is blank, this means no transaction involved on that month for a specific account. Are you confident that there will be a transaction for the 1000-9000 acounts every month cause this has something to do with your requested formula ? "Pedro AM" wrote: Hi All I need to produce a sum based on a accounts database. In column A I have accounts from 1000 to 9000 (named accsPL) In column B to M I have numbers. Each of these columns is one month in the year. The formula should sum all values where account number is greater than X and lower than Y and return the value in Column headed Z where X,Y and Z are specified in separate cells. I thought of: =SUM(IF((accsPLJ9)*(accsPL<K9),'BD PL'!D8:D12)) My problem is that I want the last part to be moveable as D8:D12 is month 3 and I want it to be the month I specify. Also, this database is a pivot table so it will grow in length every month when updated. Do you guys/girls have any idea on how to overcome this? Thank you |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM conditioned to range
Hi Bob
I am so sorry to reply so late. I have just retaken this work which is well behind now due to some personal problems. I have tried your formula but it doesn't work. I wonder if it is because accsPL and BD PL are in an external file. The formula returns #NUM! Thank you "Bob Phillips" wrote: Are you sorted? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Pedro AM" wrote in message ... Hi yes, there will be transactions every month. Thanks for your help and also to Bob "driller" wrote: Hi Pidro, you mean database of accounts where are all records are non-stagnant or non-blank. Meaning all are active and has monthly figures. Maybe i am just confused cause in my thinking even we have 0 value in record, that means there is a transaction involved. If cell record is blank, this means no transaction involved on that month for a specific account. Are you confident that there will be a transaction for the 1000-9000 acounts every month cause this has something to do with your requested formula ? "Pedro AM" wrote: Hi All I need to produce a sum based on a accounts database. In column A I have accounts from 1000 to 9000 (named accsPL) In column B to M I have numbers. Each of these columns is one month in the year. The formula should sum all values where account number is greater than X and lower than Y and return the value in Column headed Z where X,Y and Z are specified in separate cells. I thought of: =SUM(IF((accsPLJ9)*(accsPL<K9),'BD PL'!D8:D12)) My problem is that I want the last part to be moveable as D8:D12 is month 3 and I want it to be the month I specify. Also, this database is a pivot table so it will grow in length every month when updated. Do you guys/girls have any idea on how to overcome this? Thank you |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM conditioned to range
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
self-sizing adv.filter criteria range | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Wrap Text Across Columns & Rows | Excel Discussion (Misc queries) | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Help with using range names in sum function | Excel Worksheet Functions |