• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT using date ranges: How can I write it?

U

uktimike

New Member
Hi


I'm trying to work out how much a certain person needs to pay for subscriptions each month. Sumproduct is my weapon of choice as I include criteria (and I like it). The part that is throwing me is making it sum between certain dates.


How do you include dates? I have an answer that I'm trying out from another forum but it's giving me strange results. Details below:

[*] Person range($A$4:$A$300)
[*]Date range ($D$4:$D$300)
[*]Cost range ($E$4:$E$300)
Each person's name to compare to is A$22, A$23, A$24, etc.
The list of dates that define each month,e.g. $A$1=01/01/10 (start) and $B$1=31/01/10 (end). The goal is to make it sum dates BETWEEN the start and the end

Suggested formula:


Sumproduct(--(INT($D$4:$D$300)>=$A$1),--(INT($D$4:$D$300)<=$B$1),--($A$4:$A$300=$A$22),--($E$4:$E$300)


So far I'm receiving wild results. It doesn't seem to take in to account the date criteria, so any help making the formula work is greatly appreciated.


Many thanks

Mike
 
Hi Mike,


Try this


=SUMPRODUCT(--($D$4:$D$300>=$A$1),--($D$4:$D$300<=$B$1),--($A$4:$A$300=A22),($E$4:$E$300))
 
You must log in or register to reply here.
Back
Top