Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Adding the number of entries made within a certain date range

  1. #1

    Adding the number of entries made within a certain date range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 youProspectTracker.xlsx

  2. #2
    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)

  3. #3
    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

  4. #4
    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.

  5. #5
    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

  6. #6
    Hi adshfu1

    If you have Excel 2007>. You can take advantage of using the COUNTIFS & SUMIFS Functions.

  7. #7
    Quote Originally Posted by Kevin@Radstock View Post
    Hi adshfu1

    If you have Excel 2007>. You can take advantage of using the COUNTIFS & SUMIFS Functions.
    Not using an expression like MONTH(rng) you can't.

  8. #8
    Quote Originally Posted by adshfu1 View Post
    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
    Change =1 to =2 etc.

  9. #9
    Quote Originally Posted by Bob Phillips View Post
    Not using an expression like MONTH(rng) you can't.
    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.

  10. #10
    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.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •