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.

1 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])

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.