+ Reply to Thread
Results 1 to 3 of 3

count if two columns equal critera across multiple columns and rows

  1. #1
    Registered User
    Join Date
    05-31-2007
    Posts
    17

    count if two columns equal critera across multiple columns and rows

    Howdy,

    If I have the following:

    Fred none Amy early
    John late Dave late
    Kim early Sam early
    Brad none Lisa late

    I want to know how many rows where both people are listed as either "none" OR "late"

    So in this example my answer should be 2 as John & Dave both have late and Brad & Lisa have none & late.

    Thanks in advance for any help!

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi Taylor_1978,

    See attached where I've created a formula in column E and then filtered on it.

    HTH

    Robert
    Attached Files Attached Files

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    if you assume your sample data was in range A1:D4

    =SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$B$4,{"none","late"},0))),--(ISNUMBER(MATCH($D$1:$D$4,{"none","late"},0))))

    or even

    =SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$B$4&":"&$D$1:$D$4,{"none:none","early:early","none:early","early:none"},0))))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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