SUM conditioned to range

[Guest]

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((accsPL>J9)*(accsPL<K9),'BD PL'!D812))
My problem is that I want the last part to be moveable as D812 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

 

[Bob Phillips]

Try this

=SUMPRODUCT((accsPL>J9)*(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)

 

[Guest]

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 ?

 

[Guest]

Hi

yes, there will be transactions every month.

Thanks for your help and also to Bob

 

[Bob Phillips]

Are you sorted?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

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

 

[Don Guillett]

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