+ Reply to Thread
Results 1 to 8 of 8

COUNTIF Function - For every column where header equals date, count occurrences in column

  1. #1
    Registered User
    Join Date
    04-01-2016
    Location
    Providence, RI
    MS-Off Ver
    Excel 2016
    Posts
    5

    COUNTIF Function - For every column where header equals date, count occurrences in column

    Hello,

    I apologize ahead of time if this doesn't make sense at first.
    I have a table that has the date as a header across a row, and sample names down a column.
    (Sorry, I am unable to attach files currently).

    example.jpg

    I need to count the total number of samples taken during a certain month (disregarding any text). In the example table above, if I were trying to count the number of samples for January, it should be 14. For February it should be 16.

    I can use
    =COUNTIFS(B3:G12,"<>"&"",B3:G12,"<>N/A",B3:G12,"<>Down")
    and that works to count ALL samples no matter the date.
    However, I cannot figure out what to add or change in the function to count only for the specific months.

    I've tried an IF statement
    =IF(AND(B2:G2>"12/31/2015",B2:G2<"2/01/2016"),COUNTIFS(B12:G21,"<>"&"",B12:G21,"<>N/A",B12:G21,"<>Down"),0)

    I've also added empty rows and tried
    =SUM(COUNTIFS(B2:G11,">12/31/2015",B2:G11,"<2/01/2016",B12:G21,"<>"&"",B12:G21,"<>N/A",B12:G21,"<>Down"))

    I just get 0 with both.

    I feel like I am missing something simple... Any help would be appreciated.

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: COUNTIF Function - For every column where header equals date, count occurrences in col

    something like this perhaps

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    C20:G20 - are the dates
    C21:G23 - are the data under the dates
    *(MONTH(C20:G20)=1) the 1 there means January you can change it to a cell reference.

    Note: It is better to upload a real workbook than pictures so members could try the formulas out.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    04-01-2016
    Location
    Providence, RI
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: COUNTIF Function - For every column where header equals date, count occurrences in col

    I had tried to attach a file, and it wouldn't let me.

    However, I tried your function, and it worked. If there are multiple sheets, it will work for summing the sheets together as well.
    =SUMPRODUCT((Sheet1!B2:G2<>"")*(MONTH(Sheet1!B2:G2)=1)*ISNUMBER(Sheet1!B3:G12))+SUMPRODUCT((Sheet2!B2:G2<>"")*(MONTH(Sheet2!B2:G2)=1)*ISNUMBER(Sheet2!B3:G12))+SUMPRODUCT((Sheet3!B2:G2<>"")*(MONTH(Sheet3!B2:G2)=1)*ISNUMBER(Sheet3!B3:G12))

    Now I was trying to figure out a way to reduce the size of that, possibly using INDIRECT. I get #VALUE errors when I have tried that.

    Thanks so much!

  4. #4
    Registered User
    Join Date
    04-01-2016
    Location
    Providence, RI
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: COUNTIF Function - For every column where header equals date, count occurrences in col

    When clicking on the attachment icon, I just get a blank box.

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: COUNTIF Function - For every column where header equals date, count occurrences in col

    Try
    Click "Go Advance" button
    then "Manage Attachements"

  6. #6
    Registered User
    Join Date
    04-01-2016
    Location
    Providence, RI
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: COUNTIF Function - For every column where header equals date, count occurrences in col

    Thanks.

    I think I got it. Attached (hopefully) is a simplified version of the table I am working with.
    .
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: COUNTIF Function - For every column where header equals date, count occurrences in col

    Try



    For January

    =SUMPRODUCT((ISNUMBER($B$3:$G$12))*(MONTH($B$2:$G$2)=1))

    For February

    =SUMPRODUCT((ISNUMBER($B$3:$G$12))*(MONTH($B$2:$G$2)=2))

  8. #8
    Registered User
    Join Date
    04-01-2016
    Location
    Providence, RI
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: COUNTIF Function - For every column where header equals date, count occurrences in col

    Thank you for your prompt replies.

    vlady's response did work for counting all samples.

    What about if I need to count samples that were >= 3 ?

+ 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. countif value in column based on date match in header
    By lucasreece in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-15-2015, 02:36 AM
  2. [SOLVED] COUNTA function to count from first cell to date in column header
    By mr_mango81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2015, 01:03 AM
  3. Count unique occurences in column A if column B equals value
    By butler1012 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-26-2014, 09:15 AM
  4. [SOLVED] Use Countif and ?? to count occurrences of text string in a column
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2014, 03:08 PM
  5. [SOLVED] Need to count if Column A contains definded text and Column B equals defined word
    By JBlo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-26-2014, 01:38 PM
  6. Replies: 8
    Last Post: 05-15-2013, 04:50 PM
  7. Replies: 5
    Last Post: 09-18-2011, 10:44 PM

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