+ Reply to Thread
Results 1 to 4 of 4

Count using date and other criteria - formula amendment

  1. 07-02-2014, 06:55 PM #1
    Christopherdj
    Christopherdj is offline
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Count using date and other criteria - formula amendment

    With reference to the attached file.

    The "TRAINING" sheet has a formula in column "R" which checks and counts clients in the "DATA" sheet.
    Inside the formula it looks for a date in Column "O" of the "DATA" sheet equal to or greater than 01/01/2014.

    I would like use the date (Month) as the criteria shown in Column E of the "Training" sheet, still using Column "O" of the "DATA" sheet so that the result returns the count of only those within that month’s date.

    I’m hoping there is a quick fix and it’s not too difficult.
    Thanks for your help – in advance.
    Attached Files Attached Files
    Last edited by Christopherdj; 07-02-2014 at 07:25 PM.
    Reply With QuoteRegister To Reply
  2. 07-02-2014, 07:11 PM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,705

    Re: Count using date and other criteria - formula amendment

    You can change the formula in R4 to this:

    =SUMPRODUCT(--(D4=DATA!$A$2:$A$10000),--(F4=DATA!$E$2:$E$10000),--(G4=DATA!$F$2:$F$10000),--(K4=DATA!$P$2:$P$10000),--(DATA!$O$2:$O$10000>=DATE(2014,1,1)),--(E4=TEXT(DATA!$O$2:$O$10000,"mmmm")),--((DATA!$Z$2:$Z$10000="ENR / ENR")+(DATA!$Z$2:$Z$10000="COMP / PASS")+(DATA!$Z$2:$Z$10000="COMP / FAIL")>0))

    (changes shown in red), then copy down.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  3. 07-02-2014, 07:25 PM #3
    Christopherdj
    Christopherdj is offline
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Count using date and other criteria - formula amendment

    Thank you for your help, problem solved.
    Reply With QuoteRegister To Reply
  4. 07-02-2014, 07:37 PM #4
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,705

    Re: Count using date and other criteria - formula amendment

    Glad to hear it - thanks for feeding back.

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] count the values from smallest date with criteria!!! help excel formula
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2013, 11:34 AM
  2. [SOLVED] Formula how to count the date with multiple criteria.
    By felixpanganiban in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-04-2012, 10:40 AM
  3. [SOLVED] Amendment to Sum Formula
    By pauldaddyadams in forum Excel General
    Replies: 10
    Last Post: 08-31-2012, 10:42 AM
  4. Formula to count date difference based on a criteria
    By athey87 in forum Excel General
    Replies: 3
    Last Post: 03-09-2012, 05:11 PM
  5. Amendment to IF formula
    By pauldaddyadams in forum Excel General
    Replies: 25
    Last Post: 11-30-2011, 06:24 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1