Solved: Excel: Sumproduct or Nested IF statement help

[goobers]

This isn't a VBA related question, though I guess VBA could get me to my final goal.

I have an excel file with 2 tabs. The first tab simply has a list of Product Types, a beginning and end date, and a total column. The second tab has a list of numbers by month and year.

What I am trying to do is the following:

On first tab:
1) If the begin date month is less than the currentdate, and
2) If the end date month is greater than currentdate6, then

from second tab:
3) sum the months of data from Tab 2 that fall between the month of currentdate6 and the month of currentdate for each product type, but
4) is not greater than the End Date

So, in my example file, i would expect to see a total of 2500 for Type A

Thanks a bunch.

[Bob Phillips]

A n on VBA solution

=SUMPRODUCT(('sum data'!$A$3:$A$8=sumproduct!$A2)
*(DATEVALUE("01-"&'sum data'!$B$2:$Y$2&"-"&'sum data'!$B$1:$Y$1)>=sumproduct!$B2)
*(DATEVALUE("01-"&'sum data'!$B$2:$Y$2&"-"&'sum data'!$B$1:$Y$1)<=sumproduct!$C2)
*('sum data'!$B$3:$Y$8))

[goobers]

xld: thanks for quick response. one minor issue with your formula. in the portion in BOLD below, is there a way to have it reflect the last day of the month instead of the "01" day of the month?

=SUMPRODUCT(('sum data'!$A$3:$A$8=sumproduct!$A2)
*(DATEVALUE("01-"&'sum data'!$B$2:$Y$2&"-"&'sum data'!$B$1:$Y$1)>=sumproduct!$B2)
*(DATEVALUE("01-"&'sum data'!$B$2:$Y$2&"-"&'sum data'!$B$1:$Y$1)<=sumproduct!$C2)
*('sum data'!$B$3:$Y$8))

[goobers]

actually, i found a workaround that added another column of data, but gets me to my expected result. marking as solved.

thanks!!