+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    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.

  2. #2
    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.

  3. #3
    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!

  4. #4
    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.

  5. #5
    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.

  6. #6
    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.

  7. #7
    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.

  8. #8
    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.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,372

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. [SOLVED] Using Countifs/Match/Index formula to count data in different columns
    By satania in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-14-2020, 12:49 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. 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. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1