Sum Values on Sheet with Multiple Rows of Headers

Hi Chris

but if you try the normal sum a reference it to the whole column as follow, this would work :-)

For Header 1.
=SUM(A:A)

Andy :-)

I'm going to assume that the headers could be mixed in the columns, i.e. header3 could also appear in column A, in which case, try this one,

Formula:

Please Login or Register  to view this content.

This must be confirmed as an array with Shift Ctrl and Enter.

OK, WOW!!! Thanks to all. Because there is additional data in the columns, other than data directly below the headers, unlike in my example (sorry for not making that clear), the SUM solution will not work for my specifice application, but, dobracik, I thank you for your time and your reply.

I tried the SUMPRODUCT solution and this works perfectly!!! My only experience with SUMPRODUCT is when used with the Solver tool. I will have to experiment with this function more to understand it better. I appriate the responses.

I am shying away from the SUM/INDEX/MATCH CSE solution, for now, but only due to the fact that I currently do not have time to delve into it. I will certainly give it a try when time permits and work to understand these functions, so, jason.b75, your reply will not go unused and I thank you for your time.

Hi Chris, just to make it a little easier for you to understand how it works.

Using SUM with CSE is effectively the same as using SUMPRODUCT. The INDEX / MATCH part was purely to make the formula dynamic, in the sense that you don't need to specify where the last row of data is, the formula will find the last header and base the range on that.