Lookup with header conditions and dynamic range

[andrewc]

Hi,

I have sample data arranged as in columns A:D in the attached worksheet.

I would like to create a formula in cell H3 that will extract from this data the sum of the values associated with the product type named in cell G3 and from the column defined by the text that's in cells H1 and H2.

In addition to using the text in cells H1 and H2 to determine the column from which the data is to be summed, I'd also like to make this range dynamic so that it will extend as far as the penultimate cell in the column (the last cell is the total which I don't want summed).

The number of columns and rows in the actual workbook will be far more than shown here so it would be preferable to have a dynamic solution as suggested above.

This has proven to be beyond my skills so could someone kindly please suggest a solution?

Thanks!

[AlKey]

Hi andrewc,
Enter formula in cell H3

Please Login or Register  to view this content.

[andrewc]

Excellent, thank you!

[andrewc]

Something I've noticed is that the solution above doesn't consider second condition, in cell H2.

Can someone please suggest how to amend this formula to take account of that.

Thanks!

[AlKey]

Something I've noticed is that the solution above doesn't consider second condition, in cell H2.

Can someone please suggest how to amend this formula to take account of that.

Thanks!

If you referring to cells C2 and D2 ("Weight") they can't be used as criteria simply because they are not present in your data table.
another thought: Use this array formula

**Must be entered with Ctrl+Shift+Enter key combination.

Please Login or Register  to view this content.

But it won't make any difference in the outcome.

[AlKey]

Or use regular formula

Please Login or Register  to view this content.