• 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.

How to Countif with between two date

Oscarr

Member
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
 

Attachments

  • Example.xlsx
    11.5 KB · Views: 9
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.
 
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.

Hi @Luke M

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

Thanks
 
Hi ,

See the attached file.

Narayan

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

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

Hi David Evans

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

Thanks
 
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
 
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
 

Attachments

  • NEW Example - DME.xlsx
    127.4 KB · Views: 7
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

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.
 

Attachments

  • NEW Example - DME.xlsx
    123.7 KB · Views: 6
Hi @David Evans

<SNIP>

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.

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

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
 
Back
Top