Sum range of cells based on a hlookup reference

mitchv3

New Member
Joined
Apr 19, 2011
Messages
24
I have data that is entered daily, and summed weekly. As the week progresses I need to sum the 3 previous days (excluding the total column).
1) I am having trouble determining how to find the range using an HLookup of the reference. (Ex: if Ref = 8 then sum Thu-Sat for item 1)
2) I have no clue to go about finding the range when the total is in the middle. (Ex: if Ref=11 then sum Sat-Sun, but leave out the total)

Any help would be greatly appreciated. (using Excel 2010)
Excel Workbook
ABCDEFGHI
156789101112
2ThuFriSatSunTOTALMonTueWed
3Item 116182214127201815
4Item 2141220227281216
5
6
7
8Ref811
9DOWSunTue
10Item 15656
11Item 24650
Sheet1
Excel 2010
Cell Formulas
RangeFormula
F3=SUM(G3:M3)
F4=SUM(G4:M4)
B9=HLOOKUP(B8,$A$1:$V$4,2,FALSE)
B10=SUM(B3:D3)
B11=SUM(B4:D4)
C9=HLOOKUP(C8,$A$1:$V$4,2,FALSE)
C10=SUM(G3,D3:E3)
C11=SUM(G4,D4:E4)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try...

B10, confirmed with CONTROL+SHIFT+ENTER, copied down and across:

=SUM(IF(ISNUMBER(MATCH($B$1:$I$1,LARGE(IF($B$1:$I$1 < B$8,IF($B$2:$I$2<>"TOTAL",$B$1:$I$1)),{1,2,3}),0)),INDEX($B$3:$I$4,MATCH($A10,$A$3:$A$4,0),0)))

Note that if a Ref number is selected and there's less than 3 days of data available, those days will be summed.
 
Upvote 0
That works really well; however, can you step me through that equation a bit? I don't exactly understand what all it is doing.

Thanks,
Mitch
 
Upvote 0
Let's take a look at the following formula for C10...

=SUM(IF(ISNUMBER(MATCH($B$1:$I$1,LARGE(IF($B$1:$I$1 < C$8,IF($B$2:$I$2<>"TOTAL",$B$1:$I$1)),{1,2,3}),0)),INDEX($B$3:$I$4,MATCH($A10,$A$3:$A$4,0),0)))

The LARGE function returns the 3 largest Ref numbers that meet the criteria -- the Ref numbers have to be less than 11, and their corresponding value in Row 2 cannot equal "TOTAL". Here's how this part of the formula is evaluated...

LARGE(IF($B$1:$I$1 < C$8,IF($B$2:$I$2<>"TOTAL",$B$1:$I$1)),{1,2,3})

LARGE(IF({5,6,7,8,9,10,11,12} < 11,IF({"Thu","Fri","Sat","Sun","TOTAL","Mon","Tue","Wed"}<>"TOTAL",{5,6,7,8,9,10,11,12},{1,2,3})

LARGE({5,6,7,8,FALSE,10,FALSE,FALSE},{1,2,3})

{10,8,7}

This array of values is used in the MATCH function to determine which Ref numbers match these values. Here's how this part of the formula is evaluated...

MATCH($B$1:$I$1,LARGE(IF($B$1:$I$1 < C$8,IF($B$2:$I$2<>"TOTAL",$B$1:$I$1)),{1,2,3}),0)

MATCH($B$1:$I$1,{10,8,7},0)

{#N/A,#N/A,3,2,#N/A,1,#N/A,#N/A}

This array of values is then passed to ISNUMBER, which returns TRUE for each value in the array that is a number. Otherwise, it returns FALSE. Here's how this part of the formula is evaluated...

ISNUMBER(MATCH($B$1:$I$1,LARGE(IF($B$1:$I$1 < C$8,IF($B$2:$I$2<>"TOTAL",$B$1:$I$1)),{1,2,3}),0))

ISNUMBER(MATCH({5,6,7,8,9,10,11,12},{10,8,7},0))

ISNUMBER({#N/A,#N/A,3,2,#N/A,1,#N/A,#N/A})

{FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE}

The INDEX/MATCH part of the formula returns an array of values from the desired row. The row number is determined by the MATCH function. And since the column number is 0, INDEX returns an array of values for the entire row. Here's how this part of the formula is evaluated...

INDEX($B$3:$I$4,MATCH($A10,$A$3:$A$4,0),0)

INDEX($B$3:$I$4,1,0)

{16,18,22,14,127,20,18,15}

So the entire formula is evaluated something like this...

=SUM(IF(ISNUMBER(MATCH($B$1:$I$1,LARGE(IF($B$1:$I$1 < C$8,IF($B$2:$I$2<>"TOTAL",$B$1:$I$1)),{1,2,3}),0)),INDEX($B$3:$I$4,MATCH($A10,$A$3:$A$4,0),0)))

=SUM(IF(ISNUMBER(MATCH({5,6,7,8,9,10,11,12},{10,8,7},0)),{16,18,22,14,127,20,18,15}))

=SUM(IF(ISNUMBER({#N/A,#N/A,3,2,#N/A,1,#N/A,#N/A}),{16,18,22,14,127,20,18,15}))

=SUM(IF({FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE},{16,18,22,14,127,20,18,15}))

=SUM({FALSE,FALSE,22,14,FALSE,20,FALSE,FALSE})

=56
 
Upvote 0

Forum statistics

Threads
1,224,592
Messages
6,179,789
Members
452,942
Latest member
VijayNewtoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top