SUMIF with row and column conditions

Good afternoon -

I have been banging my head on the wall for 2 days now, and can't figure this one out.

I have reporting in a not so Excel-friendly format that I'm trying to summarize without any manipulation. A pivot would work great, but the format does not lend itself to that.

I have dates in Column A followed by a "Sub Total" field. I also have descriptors across the top in row 1 that I need to match, and either sum all the data (from B3:M27), or just the data in the "Sub Total" field (B29:M29). The "Sub Total" field is not static, and will change depending on the number of working days in the month, which is why I can't just reference row 29.

I've tried SUMIF, SUMPRODUCT, INDEX, MATCH - and nothing is giving me what I need.

In the attached file, and example would be to sum all values for column heading Z, which would be 11.

Any ideas???

Try this

=SUM(INDEX($B$3:$M$33,MATCH("Sub Totals:",$A$3:$A$33,0),0))

As long as the data begins in row 3.
I chose 33 basically because you said the length of the data is based on how many working days there are.
Well, there can't possibly be more than 31 working days in the month, so it will never go beyond row 33.

Thanks for the response Jonmo1.

So this gets me close. Your equation is summing everything in the Sub Totals row. Now I need to narrow in on just those values that are specific to columns with header "Z".

The MATCH function seems to work well for data in columns. Doesn't appear to do so well for data in rows.

Try

=SUMIF($B$1:$M$1,"Z",INDEX($B$3:$M$33,MATCH("Sub Totals:",$A$3:$A$33,0),0))

Eureka!

You da man!

Many thanks!

You're welcome.