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
Last edited by bkatzman; 04-29-2010 at 11:40 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")
Last edited by NBVC; 04-29-2010 at 11:20 AM.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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!
=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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks