Excel 2007 : SUMPRODUCT total

**bkatzman** · 04-29-2010, 11:15 AM

How can I modify this counting function to total the values in Column B if they meet the date/amount criteria? I thought adding ,B:B) would do it, but it errors.

=SUMPRODUCT(--(A:A>=DATE(2005,10,1)),--(A:A<=DATE(2006, 9,30)),--(B:B>=1),--(B:B<=19.99))

Thanks!
Bryce

**NBVC** · 04-29-2010, 11:17 AM

This errors?

=SUMPRODUCT(--(A:A>=DATE(2005,10,1)),--(A:A<=DATE(2006, 9,30)),--(B:B>=1),--(B:B<=19.99),B:B)

what error do you get?

although using whole range references with Sumproduct is quite resource intensive... try to limit the range or use SUMIFS

=SUMIFS(B:B,A:A,">="&DATE(2005,10,1),A:A,"<="&DATE(2006, 9,30),B:B,">=1",B:B,"<=19.99")

**bkatzman** · 04-29-2010, 11:26 AM

For some reason when I copied any pasted =SUMPRODUCT(--(A:A>=DATE(2005,10,1)),--(A:A<=DATE(2006, 9,30)),--(B:B>=1),--(B:B<=19.99),B:B) it worked!?!?

Thanks. Can you tell me how to pull data for this function from another tab "Donor Data". I know you showed me how to do this for other functions, but I am getting a NAME error when I simply add Donor Data! in front of all of the above column references.

Thanks again!

**NBVC** · 04-29-2010, 11:30 AM

=SUMPRODUCT(--('Donor Data'!A:A>=DATE(2005,10,1)),--('Donor Data'!A:A<=DATE(2006, 9,30)),--('Donor Data'!B:B>=1),--('Donor Data'!B:B<=19.99),'Donor Data'!B:B)

you need to enclose in single quotes.. because of the space between the words.

Again, you should reconcider using SUMIFS instead or use a shorter defined absolute range.