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

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

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

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

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

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

SUM conditioned to range
 

I wonder if it is because accsPL and BD PL are in an external file.
possibly?

--
Don Guillett
SalesAid Software

"Pedro AM" wrote in message
...
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