+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS refering to a table (of course)

  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    615

    COUNTIFS refering to a table (of course)

    On Sheet2 I have a table which among it's columns has a column with the date, named tblDates (of the score on a test).

    I then created another column that displays the month of the afore-said date which is then named tblTestMonth.

    This was done so on another sheet, Sheet1, I can use a COUNTIFS() that uses a month as one of the conditions, so any month in question, qryMonth, which for instance might be the number 11 for November, sort of like =COUNTIFS(tblTestMonth, qryMonth).

    I know there has to be a better way to go directly after the date without the created table and array of months, but when I try something along the lines of =COUNTIFS(MONTH(tblDates), qryMonth) it does not work, as I expected it wouldn't for I know I missed a step, I just don't know what the heck that step is.

    Though I could get by with it the way it is, when I then wish to expand the system to also accommodate the year of the test it would force me to now also create a table column for the year and that is getting way too sloppy and just plain wrong.

    Would someone be so kind as to enlighten me?

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: COUNTIFS refering to a table (of course)

    Here is one way to do it:
    =SUM(--(MONTH(tblDates)=qryMonth))
    Enter with control-shift-enter
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    615

    Re: COUNTIFS refering to a table (of course)

    Thank-you; I look forward to testing it out when I get back from work. Can you educate me a bit? I am pretty confused by the syntax, mainly the two dashes before (MONTH(tblDates). I don't want to seem like I just fell off the turnip truck, but I have never seen this before!

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: COUNTIFS refering to a table (of course)

    The '--' converts the Boolean results to 1's and 0's. '--' seems to be the norm, but '+0' or '1*' also work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. refering data from pivot tables to a table on a different sheet.
    By Tal72 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-21-2013, 03:42 PM
  2. CountIFS VS. Pivot Table
    By grandevouz in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-24-2012, 02:34 AM
  3. Need to get another data sheet table contain by refering sheet2 C3 cell name
    By harsha_manjula in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-20-2011, 05:06 PM
  4. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  5. Countifs with table references not working
    By Pauleyb in forum Excel General
    Replies: 2
    Last Post: 10-28-2011, 09:49 AM

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