adding up multiple HLOOKUP results

I did a poor job of recreating your table. As simple as it was I still made it wrong. The ranges that worked in my table failed in yours. For your original table it should have been:

A3 =SUMIFS(D3:J3,D$2:J$2,"Qty",$D$1:$J$1,"selection")

B3 =SUMIFS(E3:K3,E$2:K$2,"£",$D$1:$J$1,"selection")

Anyway, it is probably better to have the hidden row Barry suggested so you don't have to play tricks. I would suggest, though, that your hidden row contain the word "selection" in both cells (above "qty", too, not just above "£") so your formula can be the same in columns A and B (other than "qty" being replaced with the "£").

You can do that with SUMIFS.

cell A3 =SUMIFS(D3:K3,D$2:K$2,"Qty",$D$1:$K$1,"selection")

fill down to complete the column

Fill A3 to B3 to get most of that formula done.

In B3 replace "Qty" with "£"

B3 should look like =SUMIFS(E3:L3,E$2:L$2,"£",$D$1:$K$1,"selection")

Fill down with that formula to complete column B

While "merged cells" is a supported feature in Numbers, using it brings forth some disadvantages. Your experience appears to have uncovered one of them.

Here's an example built on Badunit's SUMIFS, and revised to fit your table layout.

The solution does NOT use merged cells, but does employ the standard behaviour of text in Numbers automatically flowing into an empty cell to its right if the string is too long to fit a cell in one row and the cell to the right is empty.

To fill the need for a "£" marker for money amounts to be included in the total in Column B. I've inserted a new header row (row 2) and a formula to place £ in cells one column to the right of columns showing "Selection" is row 1.

R0w 2 would be hidden in normal use.

The formula shown below the table is entered in cell A4, and can be filled down from there.

The formula in B4 is similar, and is shown below this copy of the table:

With row 2 hidden, and no cell selected. the table has this appearance.

Regards,

Barry

The formula I wrote does what you are asking. For column B the formula looks in D1:K1 for the word "selection" but is looking in E2:L2 (one column shifted to the right) for the pound symbol. This is because of the merged cells.