Calculate units by multiple criteria

[Moe2886]

Hi all, Happy Friday!
I was asked to work out a formula based on multiple conditions and for the life of me I can't work it out. I think I'm over complicating it but I was thinking an INDEX and MATCH formula would work. I know I can just do a simple lookup to get the results but in all honesty I've never seen data stored like this and if I have, I would just use a PIVOT.

In short this was what was asked of me: Show how many Units were sold for the year 2017

Variable

Jan17

Feb17

Mar17

Apr17

Jun17

Jul17

Aug17

Sep17

Oct17

Nov17

Dec17

Jan18

Feb18

Traffic

3,322 <tbody> </tbody>

3,488 <tbody> </tbody>

2,592 <tbody> </tbody>

3,007 <tbody> </tbody>

18,325 <tbody> </tbody>

16,431 <tbody> </tbody>

2,783 <tbody> </tbody>

1,933 <tbody> </tbody>

2,388 <tbody> </tbody>

2,405 <tbody> </tbody>

3,538 <tbody> </tbody>

2,988 <tbody> </tbody>

3,047 <tbody> </tbody>

Traffic

3,846 <tbody> </tbody>

2,138 <tbody> </tbody>

5,467 <tbody> </tbody>

1,225 <tbody> </tbody>

2,788 <tbody> </tbody>

1,926 <tbody> </tbody>

5,988 <tbody> </tbody>

2,450 <tbody> </tbody>

1,700 <tbody> </tbody>

2,983 <tbody> </tbody>

2,100 <tbody> </tbody>

2,900 <tbody> </tbody>

2,450 <tbody> </tbody>

Units

548 <tbody> </tbody>

566 <tbody> </tbody>

1,420 <tbody> </tbody>

541 <tbody> </tbody>

581 <tbody> </tbody>

535 <tbody> </tbody>

560 <tbody> </tbody>

387 <tbody> </tbody>

541 <tbody> </tbody>

541 <tbody> </tbody>

866 <tbody> </tbody>

560 <tbody> </tbody>

400 <tbody> </tbody>

Units

1,420 <tbody> </tbody>

541 <tbody> </tbody>

1,420 <tbody> </tbody>

387 <tbody> </tbody>

1,420 <tbody> </tbody>

541 <tbody> </tbody>

560 <tbody> </tbody>

571 <tbody> </tbody>

387 <tbody> </tbody>

560 <tbody> </tbody>

541 <tbody> </tbody>

362 <tbody> </tbody>

162 <tbody> </tbody>

Gross Rev

£5,805.36 <tbody> </tbody>

£5,247.24 <tbody> </tbody>

£2,175.64 <tbody> </tbody>

£1,716.56 <tbody> </tbody>

£2,026.92 <tbody> </tbody>

£4,717.35 <tbody> </tbody>

£1,663.83 <tbody> </tbody>

£1,808.98 <tbody> </tbody>

£2,258.34 <tbody> </tbody>

£3,354.40 <tbody> </tbody>

£4,096.14 <tbody> </tbody>

£2,479.86 <tbody> </tbody>

£2,455.90 <tbody> </tbody>

Gross Rev

£2,175.64 <tbody> </tbody>

£2,795.24 <tbody> </tbody>

£3,285.30 <tbody> </tbody>

£2,455.90 <tbody> </tbody>

£3,354.40 <tbody> </tbody>

£3,324.45 <tbody> </tbody>

£5,247.24 <tbody> </tbody>

£1,999.58 <tbody> </tbody>

£2,270.21 <tbody> </tbody>

£3,354.40 <tbody> </tbody>

£2,327.67 <tbody> </tbody>

£2,026.92 <tbody> </tbody>

£1,826.95 <tbody> </tbody>

Avg Price

£4.99 <tbody> </tbody>

£4.99 <tbody> </tbody>

£1.49 <tbody> </tbody>

£6.99 <tbody> </tbody>

£4.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£3.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£4.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£3.99 <tbody> </tbody>

Avg Price

£4.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£1.49 <tbody> </tbody>

£1.49 <tbody> </tbody>

£4.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£4.99 <tbody> </tbody>

£4.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£3.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

£3.99 <tbody> </tbody>

£5.99 <tbody> </tbody>

<tbody>
</tbody>

Anyway hopefully I can sleep at night knowing either I was on the right track with INDEX or I'm a complete fool

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Variable</td><td style="text-align:right; ">ene-17</td><td style="text-align:right; ">feb-17</td><td style="text-align:right; ">mar-17</td><td style="text-align:right; ">abr-17</td><td style="text-align:right; ">may-17</td><td style="text-align:right; ">jun-17</td><td style="text-align:right; ">jul-17</td><td style="text-align:right; ">ago-17</td><td style="text-align:right; ">sep-17</td><td style="text-align:right; ">oct-17</td><td style="text-align:right; ">nov-17</td><td style="text-align:right; ">dic-17</td><td style="text-align:right; ">ene-18</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Traffic</td><td style="text-align:right; ">3,322</td><td style="text-align:right; ">3,488</td><td style="text-align:right; ">2,592</td><td style="text-align:right; ">3,007</td><td style="text-align:right; ">18,325</td><td style="text-align:right; ">16,431</td><td style="text-align:right; ">2,783</td><td style="text-align:right; ">1,933</td><td style="text-align:right; ">2,388</td><td style="text-align:right; ">2,405</td><td style="text-align:right; ">3,538</td><td style="text-align:right; ">2,988</td><td style="text-align:right; ">3,047</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Traffic</td><td style="text-align:right; ">3,846</td><td style="text-align:right; ">2,138</td><td style="text-align:right; ">5,467</td><td style="text-align:right; ">1,225</td><td style="text-align:right; ">2,788</td><td style="text-align:right; ">1,926</td><td style="text-align:right; ">5,988</td><td style="text-align:right; ">2,450</td><td style="text-align:right; ">1,700</td><td style="text-align:right; ">2,983</td><td style="text-align:right; ">2,100</td><td style="text-align:right; ">2,900</td><td style="text-align:right; ">2,450</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Units</td><td style="text-align:right; ">548</td><td style="text-align:right; ">566</td><td style="text-align:right; ">1,420</td><td style="text-align:right; ">541</td><td style="text-align:right; ">581</td><td style="text-align:right; ">535</td><td style="text-align:right; ">560</td><td style="text-align:right; ">387</td><td style="text-align:right; ">541</td><td style="text-align:right; ">541</td><td style="text-align:right; ">866</td><td style="text-align:right; ">560</td><td style="text-align:right; ">400</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Units</td><td style="text-align:right; ">1,420</td><td style="text-align:right; ">541</td><td style="text-align:right; ">1,420</td><td style="text-align:right; ">387</td><td style="text-align:right; ">1,420</td><td style="text-align:right; ">541</td><td style="text-align:right; ">560</td><td style="text-align:right; ">571</td><td style="text-align:right; ">387</td><td style="text-align:right; ">560</td><td style="text-align:right; ">541</td><td style="text-align:right; ">362</td><td style="text-align:right; ">162</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Variable</td><td >Year</td><td >Result</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Units</td><td style="text-align:right; ">2017</td><td style="text-align:right; ">16356</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C8</td><td >=SUMPRODUCT((A2:A5=A8)*(YEAR(B1:N1)=B8)*(B2:N5))</td></tr></table></td></tr></table>

 

[Moe2886]

Hi DanteAmor,

Thank you for looking into this, it worked a treat, clearly I was way out from my thinking.

Thanks
Moe2886

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.