Excel Sum Based on Multiple Column and Row Criteria

Question

I have an excel sheet with lots of columns and rows.

I need to get a value from column E, with the header "Total."

I need to get this value using details in column B, with the header "Criteria 1." Criteria 1's value must be "abc" if I want to get the sum from column E

Usually this would be a simple sumif. However, every month the position of "Total" and "Criteria 1 move." So this month they are in columns B and E, but next month they'll be in columns C and F.

I need an excel formula that smartly looks for a column labelled "Criteria 1" and a column labelled "Total" for this sumif.

It's fine if the answer is vba only.

Answer 1

Assuming this is formatted as a table, you can accomplish it with something similar to this.

    =SUM(SUMIF( Table1[Criteria 1],"abc", Table1[Column B]), SUMIF( Table1[Criteria 1],"abc", Table1[Column E]))

edit: I think I misread slightly, it's early.

    SUMIF( Table1[Criteria 1],"abc", Table1[Total])