Sum multiple columns and rows using offset and match

MurrayBarn

New Member
Joined
May 27, 2012
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Hi

Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.

I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.

=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum

FYI:
PnL!$c$9 = is the block referred to as C9 below
$A29 contains 107000
PnL$A$10 is the block below A9 below
ControlMonth is currently set to 3

A9B9C9D9E9F9
107000
10
BEDNIGHT SALES Hotel1
(1,903,410.46)
(3,442,107.88)
(3,321,870.88)
107000
15
BEDNIGHT SALES Hotel2
(737,615.48)
(1,024,925.30)
(774,900.55)
107000
20
BEDNIGHT SALES Hotel3
(1,349,673.99)
(1,887,209.76)
(881,464.42)
107000
30
BEDNIGHT SALES Hotel4
(684,964.02)
(999,551.32)
(719,393.32)
107005
70
SALES CANCELLATION FEES RECEIV
0.00
0.00
(54,326.75)
107010
10
DISCOUNT Hotel1
18,572.03
10,046.20
10,441.81
107010
15
DISCOUNT LITTLE Hotel2
9,991.11
4,320.99
513.00
107010
20
DISCOUNT Hotel3
27,639.54
35,879.81
1,891.56
107010
30
DISCOUNT Hotel4
20,479.29
364.80
6,515.19

<tbody>
</tbody>

The answer I am looking for is (17,727,087.38) ie the Bednight Sales for all four hotels for all three months. Any ideas?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi

So you are looking to sum all rows in columns D:F where column A is 107000?

=SUMPRODUCT((PnL!$A$10:$A$500=$A29)*PnL!$D$10:$F$500)
 
Upvote 0
Thanks Firefly, however I need it to be variable, hence the offset and match function.
 
Upvote 0
Try:

=SUMPRODUCT((PnL!$A$10:$A$500=$A29)*INDEX(PnL!$D$10:$F$500,0,1):INDEX(PnL!$D$10:$F$500,0,ControlMonth))
 
Upvote 0
Assuming you mean variable in the number of columns specified, assuming D will always be included (and will extend to the number of columns represented by ControlMonth) then:

=SUMPRODUCT((PnL!$A$10:$A$500=$A29)*PnL!$D$10:INDEX(PnL!$D$10:$F$500,0,ControlMonth))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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