1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

How to Countif with between two date

Discussion in 'Ask an Excel Question' started by Oscarr, Aug 23, 2017.

  1. Oscarr

    Oscarr Member

    Messages:
    39
    Hi guys i need help,


    how to use formula Countif with between two date?

    example: Jack buy product at 01-01-17 with two time, first time is 1000, second time is 200, so show me total is 1200.so no matter how many time Jack buy at same day, also count for 1 time. if i choose date 01-01-17 to 05-01-17, Jack at 01-01-17 Three time to come buy product , 02-01-17 1 time to come buy product, 03-01-17 1 time to come buy product, 04-01-17 1 time to come buy product, 05-01-17 1 time to come buy product,so Total Jack is come to my shop for 5 time only.

    Thanks

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  3. Oscarr

    Oscarr Member

    Messages:
    39
    Hi Bro

    thanks for your help. but can this formula make between two date? mean i can choose the date?
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    See the attached file.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  5. Luke M

    Luke M Excel Ninja

    Messages:
    9,288
    Hi @Oscarr

    This is an example of a cross-post, where you post the same question on multiple threads. While asking the same questions in multiple places is both okay and encouraged, we ask that when you do so, include links in your post to the other locations so that those who come to answer your question can see what others have already suggested, and/or see if the question is already solved.

    Link to other thread:
    https://www.excelguru.ca/forums/sho...with-between-two-date-to-get-pivot-table-data

    Thanks.
  6. Oscarr

    Oscarr Member

    Messages:
    39
    Hi @Luke M

    They did not give a complete formula, therefore I repost at the other side.

    Thanks
  7. Oscarr

    Oscarr Member

    Messages:
    39
    Hi bro

    i change that formula column 23 to 1048575, because my data until there, why after i change that formula doesn't work?

    =SUM(IF(Data!$D$8:$D$1048575 = E6, IF(Data!$C$8:$C$1048575 >= $C$2, IF(Data!$C$8:$C$1048575 <= $D$2, 1/COUNTIFS(Data!$D$8:$D$1048575, E6, Data!$C$8:$C$1048575, ">=" & $C$2, Data!$C$8:$C$1048575, "<=" & $D$2, Data!$C$8:$C$1048575, Data!$C$8:$C$1048575)))))

    Thanks
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    It is not that the formula does not work ; it is that Excel may hang or crash while it does all those millions of calculations , especially if your computer RAM is not sufficient to cater to this kind of data processing.

    Do you really need to process this volume of data ? If so , you might be better off adding Power Pivot , if you wish to continue using Excel , or use Access.

    Narayan
    David Evans likes this.
  9. David Evans

    David Evans Active Member

    Messages:
    635
    If you're data range is variable, make the range Excel Table and refer to it - it will then accommodate any additions/deletions.

    I'm with N991 - if you have exactly one less than the maximum number of rows that excel can handle, you're going to be waiting a long time for the worksheet to calculate .....
  10. Oscarr

    Oscarr Member

    Messages:
    39
    Hi Narayan

    If power pivot can use you give that formula? because i need to choose date to know client come how many time, and another one (pivot table) can use you give that formula? if yes, pls teach me.

    Thanks
  11. Oscarr

    Oscarr Member

    Messages:
    39
    Hi David Evans

    thanks your Suggest. and same question, did you know pivot table can use that formula?if yes , pls teach me.

    Thanks
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    Please upload your working file with say a thousand rows of data.

    Narayan
  13. Oscarr

    Oscarr Member

    Messages:
    39
    Hi Narayan,

    i cant upload , because that file 9.15 MB...that file just same with first one i upload, the data just until downside column 1048575.

    Thanks
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    Surely you can make a new file by deleting rows 1002 till 1048576 ?

    Save this file under a new name and then upload it.

    Narayan
  15. Oscarr

    Oscarr Member

    Messages:
    39
    Hi,

    i delete rows 1002 until 1048576 already. have a look.

    Thanks

    Attached Files:

  16. Oscarr

    Oscarr Member

    Messages:
    39
    Hi @NARAYANK991

    So how is it?
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    Give me some time ; I'll get back to you.

    Narayan
  18. Oscarr

    Oscarr Member

    Messages:
    39
    Hi bro

    Thanks for your help....
  19. David Evans

    David Evans Active Member

    Messages:
    635
    Oscarr - It's hard to believe that you need to analyze 10^6 records at your store. If indeed you do, then follow @NARAYANK991's advice and use a different software package.
    However, if you're requirements are in the say 50,000 record range, you can use Excel. You might also think about storing your original Data in a different structure, e.g Date/Name/Product Type/Quantity. You will find it much easier to manipulate the data to derive the results you require.
    Which version of Excel are you using? Do you have the PowerQuery add-in? Are you open to using such a tool?

    Anyway, I've been playing around with PowerQuery this afternoon and I cam up with the attached file. I *think* it does what you're looking for - perhaps you can check and confirm that it's returning the answers you expect? If that's the case and this type of solution is amenable to you, I'll delve further into explaining what's happening in the query - there is "transformation" of your original data and some "parameterizing" going on in there

    Attached Files:

    NARAYANK991 likes this.
  20. Oscarr

    Oscarr Member

    Messages:
    39
    Hi @David Evans

    Thanks for your help.

    It's hard to believe, but is indeed.

    i'm using excel 2013 64-bit, and after i saw you say PowerQuery, i straightaway download, i can try to using PowerQuery, but you have to teach me, because i'm totally 0 of PowerQuery.

    i check that your attached file, this is almost what i expect, but you don have that date to choose like 01-01-17 to 31-03-17 total = ??

    i expect got date to choose. pls reference attached file.

    Thanks.

    Attached Files:

  21. Oscarr

    Oscarr Member

    Messages:
    39
    Hi bro @NARAYANK991

    So how is your there? Any good news for my problem?
  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    Sorry , but I have not yet got down to looking into your workbook. Probably by Monday ?

    Narayan
  23. Oscarr

    Oscarr Member

    Messages:
    39
    Hi

    Ok, wait your good news by Monday .

    Thanks
  24. David Evans

    David Evans Active Member

    Messages:
    635
    Those parameters are within the Query - it's a "parameterized" query.

    Have you been able to open the query with Power Query? If so you will see that the Data Table is laid out differently from your original - in fact it has been "unpivoted" - this makes it a lot easier to manage. Go and explore the query - it's not complicated and is essentially a simple table and the two parameters - StartDate and EndDate. If necessary, we can move those parameters to the Excel Worksheet at a later revision.

    The key to being able to report efficiently and effectively is the structure of your data. I see too many people trying to use very complex formulas in order to overcome poor data structure.

    Where are you getting your source data from? What type of system? What are the choices it offers for connecting? More than likely, you can connect your query directly to your external data.
  25. Oscarr

    Oscarr Member

    Messages:
    39
    Hi

    StartDate and EndDate is the main point what i want, i can choose that date then show me that total=?

    i'm get data from excel, because is other department send to me, so you know use StartDate and EndDate to this Power Query? if yes, pls teach me.

    Thanks

Share This Page