Formula to consolidate yearly data

Hello:

Please refer to attached file.
I have data as shown, Column A is the year and Column B is the month and rest are some sales data.
I need formula in cell O2:Q13 to consolidate the value selected from dropdown list in cell N1.
In this case i have selected "Total" which is column K
So basically consolidate column K and have in cell range O2:Q13

Let me know if you have any questions.
Thanks.

Riz

Try

=SUMPRODUCT(($E2:$K5000)*($A2:$A5000=O$1)*($B2:$B5000=$M2)*($E$1:$K$1=$N$1))

Hi Riz,

You can use a SUMIFS formula such as in cell O2:

Please Login or Register  to view this content.

Copy it down and across.

Hope this works for you.

Cheers

Hi John:

Thanks a lot...works great.
Riz

Glad I could be of some assistance.

Cheers

Opps, didn't read the whole problem. Nice formula.