PDA

View Full Version : Solved: Excel: Sumproduct or Nested IF statement help

goobers
11-09-2009, 06:08 PM
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.

xld
11-10-2009, 03:07 AM
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
11-10-2009, 10:58 AM
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
11-10-2009, 11:35 AM
actually, i found a workaround that added another column of data, but gets me to my expected result. marking as solved.

thanks!!