+ Reply to Thread
Results 1 to 4 of 4

COUNTIF on range of cells that meet 2 criteria

  1. #1
    Registered User
    Join Date
    06-15-2015
    Location
    Sheffield, UK
    MS-Off Ver
    2010
    Posts
    5

    COUNTIF on range of cells that meet 2 criteria

    Hi

    The table below is a sample of some data I am collating.

    What I'd like to do is in a separate cell count how many males have a score of 1 (then I can change this to females with 1, males with 2 etc)

    Anyone know how I could tackle this? (My full data set has 60 columns and 17 rows)

    Thanks
    Matt

    F M F F F M M M F
    3 3 2 2 3 2 2 1 3

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,911

    Re: COUNTIF on range of cells that meet 2 criteria

    Maybe ...

    =SUMPRODUCT((B1:CB1="M")*(B2:CB18=1))

  3. #3
    Registered User
    Join Date
    06-15-2015
    Location
    Sheffield, UK
    MS-Off Ver
    2010
    Posts
    5

    Re: COUNTIF on range of cells that meet 2 criteria

    Thanks for coming back so quickly.

    Tried this with one line of data (I'll be running this for each line of data) and I know there is one M that is also a 1. This came up correctly but it also did this when I changed the M to F when I know there is 0.

    Is this formula finding cells that have a 1 and either a M or an F possibly?

  4. #4
    Registered User
    Join Date
    06-15-2015
    Location
    Sheffield, UK
    MS-Off Ver
    2010
    Posts
    5

    Re: COUNTIF on range of cells that meet 2 criteria

    Tried the COUNTIFS formula instead ans weirdly this seems to work. Not sure why SUMPRODUCT didn't.

    Very weird. Anyway, thanks for your help!

+ 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 two adjacent cells meet logical criteria - help please!
    By Dave67 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-07-2017, 09:25 AM
  2. [SOLVED] Help with loop to select range of cells that meet criteria
    By NU2vba in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2016, 02:27 AM
  3. [SOLVED] Deleting rows where range of cells meet two criteria
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2015, 04:50 AM
  4. [SOLVED] countif three successive cells meet a criteria
    By FerLab in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-09-2014, 04:32 PM
  5. Paste range of cells that meet criteria
    By lorena.tgarza in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2012, 09:51 PM
  6. Using Countif to count cells that meet date criteria
    By Gooford in forum Excel General
    Replies: 2
    Last Post: 10-28-2010, 08:09 AM
  7. sum a range of cells that meet one criteria
    By shanevo6 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2007, 04:06 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