+ Reply to Thread
Results 1 to 10 of 10

Struggling with a COUNT(QUERY

  1. #1
    Registered User
    Join Date
    06-12-2019
    Location
    cape town, south africa
    MS-Off Ver
    2017
    Posts
    5

    Unhappy Struggling with a COUNT(QUERY

    Good Day All

    Im trying to count the amount of times a specific value appears in a different sheet on the same workbook
    These must be separated by month (a column in the data sheet contains the date)

    Ive been trying:
    =COUNT(QUERY(scandata, "select D where D<'6/01/2018' & D>'4/30/2018' ))"))

    seems to return a zero value when I know for a fact there are at least 4x in that month.

    Thank you very much for reading!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,180

    Re: Struggling with a COUNT(QUERY

    Hi Kyleglo and welcome to the forum,

    I've created a workbook that does what I think you want. I've used the CountIf() function instead of your Query() function. See if it works for you.

    Countif Month from Date.xlsx
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,918

    Re: Struggling with a COUNT(QUERY

    Is Query() UDF? If so, I'd recommend uploading sample workbook with UDF.

    But you can probably just use COUNTIFS(DateColumn,"<"&DateValue,DateColumn,">"&DateValue).
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    06-12-2019
    Location
    cape town, south africa
    MS-Off Ver
    2017
    Posts
    5

    Re: Struggling with a COUNT(QUERY

    Thank you so much

    Unfortunately I need to count the amount of times a Value appears dependant on the actual value (in this case VALUE is a medical "PracticeID")
    there are +2000 different practices and I need to see how many scans were done (each line on the data sheet represents a scan) with the date of said scan.

    Practices sheet needs to represent amount of scans per month
    Scans sheet has the data on it (PracticeiD & DateTime)

    Please see attached images:
    scans data.JPGpractices.JPG

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,918

    Re: Struggling with a COUNT(QUERY

    So add another criteria to COUNIFS?

    But without having full detail of what you are trying to do (along with sample workbook with data & expected output), hard to help you.

    Though I tend to use, PivotTable rather than formula for such summary.

  6. #6
    Registered User
    Join Date
    06-12-2019
    Location
    cape town, south africa
    MS-Off Ver
    2017
    Posts
    5

    Re: Struggling with a COUNT(QUERY

    Thank you, Im unsure how to use Pivot.

    in the larger pic(Data) is all the scans my business has done.
    both pages have a "PracticeID"

    I want to represent how many of the scans related to each PracticeID were in each month
    Will try COUNTIF now

    Thnak you

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,180

    Re: Struggling with a COUNT(QUERY

    Hi,

    This looks like instead of wanting to do a CountIf() you need to count multiple things so CountIfS() might be a better choice. If you could supply a sample workbook instead of a picture, I think a very easy and simple Pivot Table would do all your work. This video might be close to your problem.
    https://www.bing.com/videos/search?q...564E&FORM=VIRE

  8. #8
    Registered User
    Join Date
    06-12-2019
    Location
    cape town, south africa
    MS-Off Ver
    2017
    Posts
    5

    Re: Struggling with a COUNT(QUERY

    all sorted guys!
    thank you very much for the help, Ive used:
    =count(query(scandata,"select A where F=" & $A249 & " and D >= date '" & text(date($H$1,I$2,1),"yyyy-mm-dd" ) & "' and D < date '" & text(edate(date($H$1,I$2,1),1),"yyyy-mm-dd" ) & "'"))

  9. #9
    Registered User
    Join Date
    06-12-2019
    Location
    cape town, south africa
    MS-Off Ver
    2017
    Posts
    5

    Unhappy Re: Struggling with a COUNT(QUERY

    HI again.
    sorry, im brand new to the forum.

    Ive managed to get it to work on google sheets, however It is seriously slow and im getting nowhere..

    I cant help but wonder if my machine will calculate much faster on excel.

    Problem is this isnt compatible with excel:

    =count(query(Sheet1! A where F=" & $A3 & " and D >= date '" & text(date($H$1,H$2,1),"yyyy-mm-dd" ) & "' and D < date '" & text(edate(date($H$1,H$2,1),1),"yyyy-mm-dd" ) & "'"))

    Thank you again!
    goo to mic.JPG

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,101

    Re: Struggling with a COUNT(QUERY

    To attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1