Search code examples
excelexcel-formulaexcel-2010formulasumifs

Excel sumif with both horizontal and vertical criteria's

I have the below table

A B C D E
Posters 1-10 11-50 51-100 101-200
a £ 0.41 £ 0.41 £ 0.37 £ 0.32
b £ 0.83 £ 0.82 £ 0.73 £ 0.64
d £ 24.37 £ 16.67 £ 13.52 £ 10.12
d £ 0.50 £ 0.50 £ 0.44 £ 0.39
e £ 1.00 £ 0.99 £ 0.89 £ 0.78
Others 1-40 41-60 61-90 91-200
de £ 0.41 £ 0.41 £ 0.37 £ 0.32
fg £ 0.83 £ 0.82 £ 0.73 £ 0.64
hi £ 24.37 £ 16.67 £ 13.52 £ 10.12
jk £ 0.50 £ 0.50 £ 0.44 £ 0.39
lm £ 1.00 £ 0.99 £ 0.89 £ 0.78

I have the above examples based on these two master tables I have another tab where I select the type of product such as using the above examples

Order Form

Product Type Quantity Cost
a Poster 10 ??
de Others 60 ??

I want to basically use the cost field to work out the cost. So take the first row above as an example 'A' is within the 'Poster' table therefore I want the formula to identify this and use the weightings (1-10) to work out the cost.

What will be the best formula for this?

Solution

  • =INDEX($B$2:$E$11,MATCH(A15,$A$2:$A$11,0),MATCH(C15,--LEFT(INDEX($B$1:$E$11,MATCH(B15,$A$1:$A$11,0),),FIND("-",INDEX($B$1:$E$11,MATCH(B15,$A$1:$A$11,0),))-1),1))*C15

    It looks for the matching row first. Then for the matching column it looks for the number value prior to the - in the row of either Posters / Others

    enter image description here

    PS it's easier if you would be able to limit the headers to the lowest value only. That way the formula would be much simpler.

    I reckon Posters vs Poster was a typo and one of the Posters d should actually be c?