Sum problem with multiple criteria - Sumifs with a dynamic sum range for columns

Hi,

I´m having trouble with a huge spreadsheet. It's a little bit hard to explain so i have uploaded a sample workbook. But i will try to explain.

You could say that i need help with a sumifs, where i want the sum range to be dynamic so it changes when a certain criteria is met. Depending on whats in a cell i need to change which column to sum from. The value in the cell is the same as one of the headers for the columns.

In the uploaded workbook there are two sheets. One named "Report" and one named "Data". In the report sheet i need to calulate based on row values (depending on whats in a cell here) from different columns and rows in the Data sheet. I am pretty sure you will understand if you take a look at the uploaded workbook.

Really hoping for some assitance here although i realize i expressed myself poorly.

/Masun

Hello Masun

Does the following SUMPRODUCT function do as you require:

Formula:

Please Login or Register  to view this content.

This is in C3 copied across and down to F14.

DBY

Hi Masun,

Another approach might be to convert your data to a table and use a Pivot Table.

SumWithMultipleCriteria.xlsx

DBY, Thank you!! I am struggling a bit with the sumproduct, i dont find it intuitive at all. I have some work to do there. Once again thanks for the help!! Flyboy, thanks for making an effort in helping me out.

/Masun