Sum multiple columns and rows using offset and match

MurrayBarn

New Member
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

 A9 B9 C9 D9 E9 F9 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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)

Thanks Firefly, however I need it to be variable, hence the offset and match function.

Try:

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

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))

Brilliant! Thanks Andrew

Replies
5
Views
701
Replies
11
Views
606
Replies
2
Views
3K
Replies
1
Views
275
Replies
3
Views
263

1,196,128
Messages
6,013,616
Members
441,777
Latest member

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.

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

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