SUMIF with Index Match with multiple criteria

a777kz

New member
Joined
Sep 23, 2018
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2016
Hello.

I have the following table (assume there is data), but the format is like below:


ABCDEFG
1JuneJuly
2ActualBudgetVarianceActualBudgetVariance
3Product 1
4Product 1
5Product 1
6Product 2
7Product 3
8Product 3


On a separate tab I need to sum and lookup for all Product 1 data based on a few criteria: Month and whether Actual or Budget. Also note that month is merged across three cells (for instance June is merged and centered (B1D1). So on a separate tab if I need July and Actual, I will need to see all the sums by Product 1, 2, 3 etc.

I believe I need to use sumifs(index(match) formula, but it doesnt seem to work.

Thanks for help.
 
Here you go.

On the reference tab I need to find the sum of Product 1 based on a few criteria: month, actual and budget. It needs to be dynamic when I change a month.

Thanks
 

Attachments

  • Sample.xlsx
    185.6 KB · Views: 37
Please fill in your expected results for January on the reference tab - do this manually so we can see what you are aiming for.
 
Attached.
 

Attachments

  • Sample.xlsx
    185.6 KB · Views: 25
Nope - nothing added manually:

Excel 2016 (Windows) 32 bit
C
D
E
3
MonthJanuary
4
5
ActualBudget
6
Product 1
7
Product 2
8
Product 3
Sheet: reference
 
Should be good now.
 

Attachments

  • Sample2.xlsx
    184.9 KB · Views: 34
First, get rid of the trailing space after January on the sample tab, then:

Excel 2016 (Windows) 32 bit
C
D
E
3
MonthJanuary
4
5
ActualBudget
6
Product 1
73,319.10​
75,970.88​
7
Product 2
7,396.90​
4,789.00​
8
Product 3
18,476.93​
16,632.03​
Sheet: reference

Excel 2016 (Windows) 32 bit
D
E
6
=SUMPRODUCT((sample!$A$8:$A$17=reference!$C6)*(sample!$B$5:$L$5=reference!$D$3),sample!$B$8:$L$17)​
=SUMPRODUCT((sample!$A$8:$A$17=reference!$C6)*(sample!$B$5:$L$5=reference!$D$3),OFFSET(sample!$B$8:$L$17,0,1))​
Sheet: reference
 
Yes thanks a lot! Just tested it.

Now actually I need a slightly different calculation, instead of giving me a total by product, i just need to lookup for a value from the Sample tab for each Product listed on the reference tab. I hardcoded numbers that I need to get. Same filters: month, actual, budget.

Can you please assist. Thanks
 

Attachments

  • Sample3.xlsx
    185.1 KB · Views: 37
Can assist with the last question please?
 
Back
Top