Adding the number of entries made within a certain date range

A

adshfu1

New member
Joined
Dec 12, 2012
Messages
6
Reaction score
0
Points
0
I have attached the sheet for you.
I enter the Closing Date in Column A
The Loan Amount in Column C

I am looking to track the number of Clients within each calendar month as well as the total of the loan amounts for each month.
So for January, the counter would reflect 3 Leads for $440,000.
February would reflect 2 Clients for $325,000
I will use this sheet throughout the year and would like to breakdown each month's total in their own individual cells.

Thank youView attachment ProspectTracker.xlsx
 
For January

=SUMPRODUCT(--($A$2:$A$13<>""),--(MONTH($A$2:$A$13)=1))

=SUMPRODUCT(--($A$2:$A$13<>""),--(MONTH($A$2:$A$13)=1),$C$2:$C$13)
 
Thanks for reply.
I have copy and pasted these 2 codes however both results come back with zero?
I would be creating 24 different cells to input results in.
Am I missing something with your response?
To clarify, I would be creating the results in cells as follows:

B15 would have total # of Clients for Januaray
B16 would have total # of Clients for February, etc.

D15 total loan amount volume closed in January
D16 total loan amount volume closed in February, etc.

How do I have it pull the figures based on those month date ranges?

Thanks
 
Those formulae were only for January, would need adjusting for nay other mo nth, but both returned the correct result against your data so I don't know what you are doing that returns 0.
 
OK I did try it again and it worked. Not sure what I was doing.
OK, so how do I adjust for the other months?
Thanks again
 
Hi adshfu1

If you have Excel 2007>. You can take advantage of using the COUNTIFS & SUMIFS Functions.
 
adshfu1 said:
OK I did try it again and it worked. Not sure what I was doing.
OK, so how do I adjust for the other months?
Thanks again
Click to expand...

Change =1 to =2 etc.
 
Bob Phillips said:
Not using an expression like MONTH(rng) you can't.
Click to expand...

Well then "Know it All". Perhaps, you need to buy a book or may be Google "SUMIFS, COUNTIFS etc" and you might learn a bit!

Number of clients per month: =COUNTIFS(A2:A13,">=01/01/2013",A2:A13,"<=31/01/2013")
Total for January: =SUMIFS(C2:C13,A2:A13,">=01/01/2013",A2:A13,"<=31/01/2013")
This is just 2 ways of many using the SUMIFS & COUNTIFS, you could put dates in cells and reference them etc.

The OP wanted to track the number of clients in a month & a total for each month. I gave them a answer using the SUMIFS & COUNTIFS as they uploaded a xlsx file! You are the one who gave an answer for Excel 2003< using the SUMPRODUCT. Did the OP mention using a MONTH (rng), no I do not think so it was yourself. Perhaps as a "mod"! you should be open to all avenues of how to solve problems and not knock down anyone who comes up with an idea.
 
Before you slag me off, you would do well to read what was written. I wrote

'Not using an expression like MONTH(rng) you can't.'

Argue that I have made an erroneous statement if you can, rather than arguing a completely different case, I am happy for you to prove your skill and ability.

You chose to give the OP an option without explaining how to achieve his requirement, which is not obvious should they read the help for SUMIFS and COUNTIFS.

On another point, being a know it all, I also know how to achieve the same result with SUMIF and COUNTIF, do you?

Oh, continuing your education, maybe is one word in the context that you used it, not two.
 
Now Now LOL

Perhaps you could be a bit more civil as a "mod"! and not posting abrupt posts.
'Not using an expression like MONTH(rng) you can't.'

Hey and whats all this about."On another point, being a know it all, I also know how to achieve the same result with SUMIF and COUNTIF, do you?". Like being back to school all over again!
 
You must log in or register to reply here.
Back
Top