Need help with lookup & sum range
 

Hi Everyone,

I had a question on this table a couple days ago but now I have a new twist.
Tom gave me a formula that will search based on three criteria. What I need
now is I want to be able to search based on a certain Date & LOC and then a
range of accounts (For example 1001,1002,1003) and then have it come up with
the sum of these values from the "Activity column". The problem I face here
is getting it to do the range of accounts. On the sheet I am using I will
have 15 accounts or more I will need to have searched. My example table is
below

Date LOC ACCT Beg Bal Activity Ending
May-06 M101 1001 10.00 210.00 310.00
May-06 M101 1002 20.00 220.00 320.00
May-06 M101 1003 30.00 230.00 330.00
May-06 M101 1004 40.00 240.00 340.00
May-06 M101 1005 50.00 250.00 350.00
Jun-06 M101 1001 60.00 260.00 360.00
Jun-06 M101 1002 70.00 270.00 370.00
Jun-06 M101 1003 80.00 280.00 380.00
Jun-06 M101 1004 90.00 290.00 390.00
Jun-06 M101 1005 100.00 300.00 400.00
May-06 M102 1001 110.00 310.00 410.00
May-06 M102 1002 120.00 320.00 420.00
May-06 M102 1003 130.00 330.00 430.00
May-06 M102 1004 140.00 340.00 440.00
May-06 M102 1005 150.00 350.00 450.00
Jun-06 M102 1001 160.00 360.00 460.00
Jun-06 M102 1002 170.00 370.00 470.00
Jun-06 M102 1003 180.00 380.00 480.00
Jun-06 M102 1004 190.00 390.00 490.00
Jun-06 M102 1005 200.00 400.00 500.00


Thanks
Mascot

Need help with lookup & sum range
 

Assuming your data is on Sheet2!A1:F10, and
B6 = Date
B7 = Location
B8 = Lower Limit of Account Range (inclusive)
B9 = Upper Limit of Account Range (inclusive)

=SUMPRODUCT(--(Sheet2!A$1:A$10=B6), --(Sheet2!B$1:B$10=B7),
--(Sheet2!C$1:C$10=B8), --(Sheet2!C$1:C$10<=B9), Sheet2!E$1:E$10)

Adjust ranges as needed.

"Mascot" wrote:

Hi Everyone,

I had a question on this table a couple days ago but now I have a new twist.
Tom gave me a formula that will search based on three criteria. What I need
now is I want to be able to search based on a certain Date & LOC and then a
range of accounts (For example 1001,1002,1003) and then have it come up with
the sum of these values from the "Activity column". The problem I face here
is getting it to do the range of accounts. On the sheet I am using I will
have 15 accounts or more I will need to have searched. My example table is
below

Date LOC ACCT Beg Bal Activity Ending
May-06 M101 1001 10.00 210.00 310.00
May-06 M101 1002 20.00 220.00 320.00
May-06 M101 1003 30.00 230.00 330.00
May-06 M101 1004 40.00 240.00 340.00
May-06 M101 1005 50.00 250.00 350.00
Jun-06 M101 1001 60.00 260.00 360.00
Jun-06 M101 1002 70.00 270.00 370.00
Jun-06 M101 1003 80.00 280.00 380.00
Jun-06 M101 1004 90.00 290.00 390.00
Jun-06 M101 1005 100.00 300.00 400.00
May-06 M102 1001 110.00 310.00 410.00
May-06 M102 1002 120.00 320.00 420.00
May-06 M102 1003 130.00 330.00 430.00
May-06 M102 1004 140.00 340.00 440.00
May-06 M102 1005 150.00 350.00 450.00
Jun-06 M102 1001 160.00 360.00 460.00
Jun-06 M102 1002 170.00 370.00 470.00
Jun-06 M102 1003 180.00 380.00 480.00
Jun-06 M102 1004 190.00 390.00 490.00
Jun-06 M102 1005 200.00 400.00 500.00


Thanks
Mascot

Need help with lookup & sum range
 

If you wanted specific accounts that were non-contiguous (say 1001 and 1003)
you could use:

=SUMPRODUCT(--(Sheet2!A$1:A$10=B6), --(Sheet2!B$1:B$10=B7),
--ISNUMBER(MATCH(Sheet2!C$1:C$10,{1001,1003},0)), Sheet2!E$1:E$10)

And you could replace {1001,1003} with a reference to cells that contained
those numbers (such as B8:B9).


"JMB" wrote:

Assuming your data is on Sheet2!A1:F10, and
B6 = Date
B7 = Location
B8 = Lower Limit of Account Range (inclusive)
B9 = Upper Limit of Account Range (inclusive)

=SUMPRODUCT(--(Sheet2!A$1:A$10=B6), --(Sheet2!B$1:B$10=B7),
--(Sheet2!C$1:C$10=B8), --(Sheet2!C$1:C$10<=B9), Sheet2!E$1:E$10)

Adjust ranges as needed.

"Mascot" wrote:

Hi Everyone,

I had a question on this table a couple days ago but now I have a new twist.
Tom gave me a formula that will search based on three criteria. What I need
now is I want to be able to search based on a certain Date & LOC and then a
range of accounts (For example 1001,1002,1003) and then have it come up with
the sum of these values from the "Activity column". The problem I face here
is getting it to do the range of accounts. On the sheet I am using I will
have 15 accounts or more I will need to have searched. My example table is
below

Date LOC ACCT Beg Bal Activity Ending
May-06 M101 1001 10.00 210.00 310.00
May-06 M101 1002 20.00 220.00 320.00
May-06 M101 1003 30.00 230.00 330.00
May-06 M101 1004 40.00 240.00 340.00
May-06 M101 1005 50.00 250.00 350.00
Jun-06 M101 1001 60.00 260.00 360.00
Jun-06 M101 1002 70.00 270.00 370.00
Jun-06 M101 1003 80.00 280.00 380.00
Jun-06 M101 1004 90.00 290.00 390.00
Jun-06 M101 1005 100.00 300.00 400.00
May-06 M102 1001 110.00 310.00 410.00
May-06 M102 1002 120.00 320.00 420.00
May-06 M102 1003 130.00 330.00 430.00
May-06 M102 1004 140.00 340.00 440.00
May-06 M102 1005 150.00 350.00 450.00
Jun-06 M102 1001 160.00 360.00 460.00
Jun-06 M102 1002 170.00 370.00 470.00
Jun-06 M102 1003 180.00 380.00 480.00
Jun-06 M102 1004 190.00 390.00 490.00
Jun-06 M102 1005 200.00 400.00 500.00


Thanks
Mascot