+ Reply to Thread
Results 1 to 9 of 9

Formula to count rows where three columns match and AND and OR rule.

  1. 12-14-2019, 06:03 AM #1
    rcx116
    rcx116 is offline
    Registered User
    Join Date
    12-14-2019
    Location
    London, UK
    MS-Off Ver
    O365
    Posts
    6

    Formula to count rows where three columns match and AND and OR rule.

    I'm currently using the following formula that I found on Google. It counts how many rows have value "Car" in column C:C OR have value 1 in column F:F. The formula seems to work great:

    =SUMPRODUCT(N(('Form responses'!C:C="Car")+('Form responses'!F:F=1)>0))

    I now need to do the same count, but this time only count rows where ((Column C:C has value Car OR column F:F has value 1) AND column B:B has a date within the last two weeks)
    Please notice the nesting. First the OR rule is applied, and then the AND.

    Many thanks in advance!
    Last edited by rcx116; 12-14-2019 at 07:15 AM.
    Reply With QuoteRegister To Reply
  2. 12-14-2019, 06:14 AM #2
    BanginMyHeadOnMyDesk
    BanginMyHeadOnMyDesk is offline
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Formula to count rows where three columns match and AND and OR rule.

    I wouldn't recommend using SUMPRODUCT on entire columns like that. It will slow things down drastically.

    Perhaps try this
    =SUM(COUNTIFS(C:C,{"Car",1},F:F,">"&TODAY()-14,F:F,"<="&TODAY()))

    Beth.
    Reply With QuoteRegister To Reply
  3. 12-14-2019, 07:08 AM #3
    rcx116
    rcx116 is offline
    Registered User
    Join Date
    12-14-2019
    Location
    London, UK
    MS-Off Ver
    O365
    Posts
    6
    Hi Beth is that formula correct as Column B:B has the date that needs to be in last two weeks for the row to be counted but the columndoesn't appear in the formula.
    Last edited by AliGW; 12-14-2019 at 07:17 AM. Reason: Please don't quote unnecessarily!
    Reply With QuoteRegister To Reply
  4. 12-14-2019, 07:28 AM #4
    BanginMyHeadOnMyDesk
    BanginMyHeadOnMyDesk is offline
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Formula to count rows where three columns match and AND and OR rule.

    Apologies, I completely misread it!

    Could there be instances where C = Car AND F = 1?

    Beth.
    Reply With QuoteRegister To Reply
  5. 12-14-2019, 07:43 AM #5
    BanginMyHeadOnMyDesk
    BanginMyHeadOnMyDesk is offline
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Formula to count rows where three columns match and AND and OR rule.

    You could add the date check to your SUMPRODUCT

    =SUMPRODUCT(N(('Form responses'!C:C="Car")+('Form responses'!F:F=1)>0)*('Form responses'!B:B>TODAY()-14)*('Form responses'!B:B<=TODAY()))

    But again, I wouldn't recommend SUMPRODUCT on entire columns.

    You could do a COUNTIFS + COUNTIFS

    =COUNTIFS('Form responses'!C:C,"Car",'Form responses'!B:B,">"&TODAY()-14,'Form responses'!B:B,"<="&TODAY())+=COUNTIFS('Form responses'!F;F,1,'Form responses'!B:B,">"&TODAY()-14,'Form responses'!B:B,"<="&TODAY())

    That may not work if there will be instances of C = Car and F = 1 on the same row as it will double count. But you could get around that by adding another criteria to check the other column.

    =COUNTIFS('Form responses'!C:C,"Car",'Form responses'!F;F,"<>1","'Form responses'!B:B,">"&TODAY()-14,'Form responses'!B:B,"<="&TODAY())+=COUNTIFS('Form responses'!F;F,1,'Form responses'!C:C,"<>Car",'Form responses'!B:B,">"&TODAY()-14,'Form responses'!B:B,"<="&TODAY())

    Beth.
    Reply With QuoteRegister To Reply
  6. 12-14-2019, 10:17 AM #6
    rcx116
    rcx116 is offline
    Registered User
    Join Date
    12-14-2019
    Location
    London, UK
    MS-Off Ver
    O365
    Posts
    6
    Quote Originally Posted by BanginMyHeadOnMyDesk View Post
    Apologies, I completely misread it!

    Could there be instances where C = Car AND F = 1?

    Beth.
    Yes such instances exist.
    Reply With QuoteRegister To Reply
  7. 12-14-2019, 10:50 AM #7
    BanginMyHeadOnMyDesk
    BanginMyHeadOnMyDesk is offline
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Formula to count rows where three columns match and AND and OR rule.

    And what is the desired result in those instances?

    Did any of the formulas above work for you?

    Beth.
    Reply With QuoteRegister To Reply
  8. 12-14-2019, 10:55 AM #8
    rcx116
    rcx116 is offline
    Registered User
    Join Date
    12-14-2019
    Location
    London, UK
    MS-Off Ver
    O365
    Posts
    6
    Quote Originally Posted by BanginMyHeadOnMyDesk View Post
    You could add the date check to your SUMPRODUCT

    =SUMPRODUCT(N(('Form responses'!C:C="Car")+('Form responses'!F:F=1)>0)*('Form responses'!B:B>TODAY()-14)*('Form responses'!B:B<=TODAY()))

    But again, I wouldn't recommend SUMPRODUCT on entire columns.
    Thanks Beth that new SumProduct() worked. Why is SumProduct() not recommended for entire colums? Is it resource intensive? Theres very little data in the sheet and it's on Google Sheets so should be Ok for now.
    Reply With QuoteRegister To Reply
  9. 12-14-2019, 10:57 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,363

    Re: Formula to count rows where three columns match and AND and OR rule.

    If this is about Google Sheets, it's in the wrong forum section - shall I move the thread for you?

    Using whole column references is BAD PRACTICE with ANY formula, but especially ones that calculate using arrays. Get out of the habit of using them (full column references, that is).
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    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] Using Countifs/Match/Index formula to count data in different columns
    By satania in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-09-2023, 04:00 AM
  2. [SOLVED] Formula count if values match in two columns
    By MIGARDEIN in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2015, 04:15 PM
  3. Increment columns in INDEX and MATCH formula - ROWS and COLUMNS
    By nickmangan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2012, 10:38 AM
  4. Rule to match column data with one of two other columns
    By jtb12 in forum Excel General
    Replies: 2
    Last Post: 06-29-2012, 05:17 PM
  5. Dynamic Count If Formula with additional columns and rows.
    By nwd9s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2011, 09:12 PM
  6. Excel 2007 : Formula for muliplying rows as a rule
    By Paul_F in forum Excel General
    Replies: 4
    Last Post: 01-06-2009, 03:48 AM
  7. need formula to count number of rows based on 2 columns
    By neowok in forum Excel General
    Replies: 0
    Last Post: 01-05-2005, 09:31 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