sumproduct for two ranges

[stewart1]

Hi, I hope someone can help me here.

I'm using 2003 and I am trying to use sumproduct for the following but having no luck.

I have a list of dates (C2:C31) running from 01/02/2014 to 02/03/2014.

I have a list of varying ages (D2:D31)

K11:K15 has age ranges, e.g. 18-25.

I need to take a date range of 01/02/2014-28/02/2014 then count how many occurrences of 18-25 there are in that given date range (the formula being in L11)

I can sumproduct a date range but I cannot lend it to an age range if you will.

I've posted a dummy book if someone could have a look.

I hope someone can help, googling and cobbling together formulas has not helped my cause!

Thanks

[TMS]

One way:

Please Login or Register  to view this content.

Regards, TMS

[stewart1]

Ahh, absoultely stunning! I've sat for six hours trying to do this. You are an absolute STAR! Mad- adding left and right.... never would have got that!

[TMS]

You're welcome. Thanks for the rep.

It would, of course, be simpler if you had split the lower and upper age ranges into separate cells/columns. But, whatever, ...



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.