• 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

rahulshewale1 said:
Hii @Oscar ,

Please find the attached solution using power quary .Please Go through below step

download excel files below link
https://www.dropbox.com/s/bqsuqtkpjxwpxp1/Distinctcount with Power Quary(Final).xlsm?dl=0

Step 1
1.Click anywhere in table.
2.You will see Worksheet Queries
3.Right Click on Distinct-count Queries
4.Click on Edit
5.you will see Query setting tab on Left side
6.Click on "start date _End Date" (in Applied steps)
7.Change start date and End date as your requirement
8.Click on "Close & Load "
9.you will get Distinct Count.


For Sum of of Product
1.Right Click on Sumif _table Queries
2.Click on Edit
3.you will see Query setting tab on Left side
4.Click on "start date _End Date" (in Applied steps)
5.Change start date and End date as your requirement
6.Click on "Close & Load "

Get Final result
1.Click on Refresh Button .

Note : it will take 90 second for refresh

Regard
Rahul shewale
View attachment 45090
Click to expand...

Hi bro @rahulshewale1

This is exactly what i want....you are very pro, and the method that you share is very clear...thank you very much bro..
But I did tried yesterday, but is a little bit different. I'm still in the midst of trying, will let you know if I can't ..

Thanks
 
rahulshewale1 said:
Hii @Oscar ,

Please find the attached solution using power quary .Please Go through below step

download excel files below link
https://www.dropbox.com/s/bqsuqtkpjxwpxp1/Distinctcount with Power Quary(Final).xlsm?dl=0

Step 1
1.Click anywhere in table.
2.You will see Worksheet Queries
3.Right Click on Distinct-count Queries
4.Click on Edit
5.you will see Query setting tab on Left side
6.Click on "start date _End Date" (in Applied steps)
7.Change start date and End date as your requirement
8.Click on "Close & Load "
9.you will get Distinct Count.


For Sum of of Product
1.Right Click on Sumif _table Queries
2.Click on Edit
3.you will see Query setting tab on Left side
4.Click on "start date _End Date" (in Applied steps)
5.Change start date and End date as your requirement
6.Click on "Close & Load "

Get Final result
1.Click on Refresh Button .

Note : it will take 90 second for refresh

Regard
Rahul shewale
View attachment 45090
Click to expand...

Hi bro @rahulshewale1

i have two question to ask you.

1. why is the existing format already have date column, but why do you still insert a date column and delete the existing one?

2. why did u added in custom and remove it again?

Thanks
 

Attachments

  • h.png
    h.png
    16.2 KB · Views: 14
  • hh.png
    hh.png
    16 KB · Views: 13
Hii @Oscar

I am sorry ..I left from office ..I do not have laptop and computer in my home .
I will share answer in detail. With screenshot..

Regard
Rahul Shewale
 
Hii,

Question 1 .Inserted date
Answer : I just date format from
01-01-2017 00:00:00 to 01-01-2017

Qustion 1 : Remove column
Answer : Remove Column of date as format this
01-01-2017 00:00:00

upload_2017-9-4_10-28-45.png


Question 2 : Added custom
Answer : there is no use .i am trying to use Conditional Formula


Question 2 : Remove column 1
Answer : there is no use .i am trying to use Conditional Formula



Regard
Rahul shewale
 
rahulshewale1 said:
Hii,

Question 1 .Inserted date
Answer : I just date format from
01-01-2017 00:00:00 to 01-01-2017

Qustion 1 : Remove column
Answer : Remove Column of date as format this
01-01-2017 00:00:00

View attachment 45152


Question 2 : Added custom
Answer : there is no use .i am trying to use Conditional Formula


Question 2 : Remove column 1
Answer : there is no use .i am trying to use Conditional Formula



Regard
Rahul shewale
Click to expand...

Hi @rahulshewale1

i see..thanks bro

i want ask how to merge if i got another file?

example: now you give me that file is (Name Distinct count Product A Product B)
i still have (Product C Product D Product E Product F)in different File, Need to add in..so how to merge them?

Pls refer attached file.

Thanks
 

Attachments

  • Example.xlsx
    11.6 KB · Views: 1
hii @Oscar ,

Please send me your Final worksheet.so i might help better .

How many sheet in your workbook ?
How many Product are available in your workbook ?


Regard
Rahul shewale
 
rahulshewale1 said:
hii @Oscar ,

Please send me your Final worksheet.so i might help better .

How many sheet in your workbook ?
How many Product are available in your workbook ?


Regard
Rahul shewale
Click to expand...

Hi @rahulshewale1

I got 32 product in different 16 excel, so i make them in this example file with little data... because too many data cant upload.

Thanks
 

Attachments

  • Example.xlsx
    30 KB · Views: 1
hii @Oscar

Please find the attached sheet with Desire output.

Process :1.Convert Data Range Into "Table"
2.Go to Append Select "Three Or more tables option "
3. Select Table one by one And "Add" Then Ok
4.you will see Query setting tab on Left side
5.Remove "product Column"
6.Go to "Add custom tab " select "Custom Column"
Copy formula
if[Date]>=#date(2017,1,1) and [Date]<=#date(2017,01,07)then 1 else 0 and paste 7.Change start date and End date as your requirement8.Group by Option (screen shot attached )
9.Click on "Close & Load "
10.you will get Distinct Count.

Process :
Go to Append Select "Three Or more tables option "
1. Select Table one by one And "Add" Then Ok
2.you will see Query setting tab on Left side3.Go to "Add custom tab " select "Custom Column"
Copy formula
if[Date]>=#date(2017,1,1)and [Date]<=#date(2017,01,07)then 1 else 0 and paste (Change start date and End date as your requirement)
4.Group by Option (screen shot attached )
5.Close and load connection only.


Process : Merge (Screen Shot attached )

1. Go to Power Quary Tab
2. Select "Merge" Option
3. Select "Distinct Count" from drop down list
4.Scroll down Select "Append (sumifs)" from drop down list5.Ok
6.Click on Close & Load

Note : if any quary Please let me know.



Regard
Rahul shewale



 

Attachments

  • Power Quary Screen shot.pdf
    324.3 KB · Views: 3
  • Distinctcount with Power Quary(Final) New -(append &Merge).xlsx
    149.6 KB · Views: 2
rahulshewale1 said:
hii @Oscar

Please find the attached sheet with Desire output.

Process :1.Convert Data Range Into "Table"
2.Go to Append Select "Three Or more tables option "
3. Select Table one by one And "Add" Then Ok
4.you will see Query setting tab on Left side
5.Remove "product Column"
6.Go to "Add custom tab " select "Custom Column"
Copy formula
if[Date]>=#date(2017,1,1) and [Date]<=#date(2017,01,07)then 1 else 0 and paste 7.Change start date and End date as your requirement8.Group by Option (screen shot attached )
9.Click on "Close & Load "
10.you will get Distinct Count.

Process :
Go to Append Select "Three Or more tables option "
1. Select Table one by one And "Add" Then Ok
2.you will see Query setting tab on Left side3.Go to "Add custom tab " select "Custom Column"
Copy formula
if[Date]>=#date(2017,1,1)and [Date]<=#date(2017,01,07)then 1 else 0 and paste (Change start date and End date as your requirement)
4.Group by Option (screen shot attached )
5.Close and load connection only.

Process : Merge (Screen Shot attached )

1. Go to Power Quary Tab
2. Select "Merge" Option
3. Select "Distinct Count" from drop down list
4.Scroll down Select "Append (sumifs)" from drop down list5.Ok
6.Click on Close & Load

Note : if any quary Please let me know.


Regard
Rahul shewale
Click to expand...

Hi @rahulshewale1

Bro this is awesome...

1 got 2 question.

1. i saw that filtered Row1 is nothing there.

2. how to change this number type(9,483.80) at power query there?

Thanks
 

Attachments

  • e.png
    e.png
    83.3 KB · Views: 6
rahulshewale1 said:
hii @Oscar

Please find the attached sheet with Desire output.

Process :1.Convert Data Range Into "Table"
2.Go to Append Select "Three Or more tables option "
3. Select Table one by one And "Add" Then Ok
4.you will see Query setting tab on Left side
5.Remove "product Column"
6.Go to "Add custom tab " select "Custom Column"
Copy formula
if[Date]>=#date(2017,1,1) and [Date]<=#date(2017,01,07)then 1 else 0 and paste 7.Change start date and End date as your requirement8.Group by Option (screen shot attached )
9.Click on "Close & Load "
10.you will get Distinct Count.

Process :
Go to Append Select "Three Or more tables option "
1. Select Table one by one And "Add" Then Ok
2.you will see Query setting tab on Left side3.Go to "Add custom tab " select "Custom Column"
Copy formula
if[Date]>=#date(2017,1,1)and [Date]<=#date(2017,01,07)then 1 else 0 and paste (Change start date and End date as your requirement)
4.Group by Option (screen shot attached )
5.Close and load connection only.

Process : Merge (Screen Shot attached )

1. Go to Power Quary Tab
2. Select "Merge" Option
3. Select "Distinct Count" from drop down list
4.Scroll down Select "Append (sumifs)" from drop down list5.Ok
6.Click on Close & Load

Note : if any quary Please let me know.


Regard
Rahul shewale
Click to expand...

Hi Bro @rahulshewale1

another question:

1: why i add new name in file-A , this only auto show in (table1 connection only)there, and wont be auto show in Merge1 that table?

Pls refer attached file.

Thanks p.png pp.png ppp.png
 
Hii @Oscar,

Question 1. i saw that filtered Row1 is nothing there.
Answer : Filter only Number 1
upload_2017-9-5_10-23-46.png


Question 2 :how to change this number type(9,483.80) at power query there?
Answer : Right Click on table >>Format Cell >>>Number >> Decimal Place 2>>Use 1000 Separator (,)
upload_2017-9-5_10-32-42.png
Question 3 :
why i add new name in file-A , this only auto show in (table1 connection only)there, and wont be auto show in Merge1 that table?

Answer : Name KC Is not showing because of star date (2017,01,01 ) and End date (2017,01,07)

KC is not between date .


Regard
Rahul shewale
 
rahulshewale1 said:
Hii @Oscar,

Question 1. i saw that filtered Row1 is nothing there.
Answer : Filter only Number 1
View attachment 45191


Question 2 :how to change this number type(9,483.80) at power query there?
Answer : Right Click on table >>Format Cell >>>Number >> Decimal Place 2>>Use 1000 Separator (,)
View attachment 45192
Question 3 :
why i add new name in file-A , this only auto show in (table1 connection only)there, and wont be auto show in Merge1 that table?

Answer : Name KC Is not showing because of star date (2017,01,01 ) and End date (2017,01,07)

KC is not between date .


Regard
Rahul shewale
Click to expand...

Hi @rahulshewale1

Answer : Filter only Number 1

at Distinct Count there filtered Row1 is nothing, at Append1(for sumifs) there yes is filtered number 1.

Pls refer attached file.

Thanks
 

Attachments

  • U.png
    U.png
    88.5 KB · Views: 8
rahulshewale1 said:
Hii @Oscar,

I hope your problem solve .
Please find the attached solution with Dynamic.

Just change start date and end date

View attachment 45281


Regard
Rahul shewale
Click to expand...

Hi bro @rahulshewale1

Sorry for late reply..
This is awesome...thanks bro....Before that need to go open that query table only can change date, is too
troublesome...I try to make this start date and end date, if got any problem I will get back to you...
And I want to ask you, is it possible to add one more search box to search 1by1?

Thanks
 
You must log in or register to reply here.
Back
Top