sumifs function to calculate dynamic range sum problem

Hi

I've an excel workbook which consists of summary of product category along with states and months in columns. I need Year-to-Date total based on the drop downs for month and state. I've attached the workbook wherein I need a formula to pick up the YTD total for a particular Product Category.

It'd be great if the running total can be calculated using SUMIF but, so far, I've not succeeded in the same.

Thanks in advance.Product Category Summary.xlsx

Hi xs2deepak,

Try this formula... =SUM(($B$4:$B$12=J5)*($C$4:$C$12=$K$1)*INDIRECT("$"&CHAR(67+MATCH($J$1,$D$3:$G$3,0))&"$4:$"&CHAR(67+MATCH($J$1,$D$3:$G$3,0))&"$12"))

Note: I had to change one of your Product Categories...

I'm attaching your sample spreadsheet...

Let me know if you have any question...

re-post: beautiful formula, mama!

Perfect!!! Thanks a lot mama! and djapigo! Really appreciate these solutions.

As I've very big original dataset with me, therefore, don't want to use Indirect (Volatile) function.

Just wanted to know if it can be done through SUM/SUMIFS/INDEX combination.

Oops!!

I think the totals are not Year-To-Date. e.g. If I select Jun'12 in the drop-down, the YTD Total should give the sum of Apr'12, May'12 and Jun'12. I think this is not working out in the worksheet.

Many thanks Toni! & icestationzbra! Works perfectly!!! Appreciate these!!!