+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS formula multiple criteria

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    san diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    COUNTIFS formula multiple criteria

    Hi...I have a sheet as below and I'd like to use COUNTIFS to count records with 3 true criteria:


    980 RE H 18-Aug-10 Pos H 23-Aug-10
    980 RE H 29-Dec-10 Pos H 3-Jan-11
    988 RE H 10-Aug-10 Pos H 10-Aug-10
    988 RE H/A 12-Aug-10 Pos H 10-Aug-10
    992 RE H 4-Mar-09 Pos H 10-Mar-09
    997 RE H 5-Jan-05 Pos H 5-Jan-05
    997 RE H 16-Feb-05 Pos H 22-Feb-05
    1022 RE H 30-Mar-06 Pos H 4-Apr-06
    1028 RE H 4-Apr-12 Pos H 10-Apr-12
    1033 RE H 28-Apr-05 Pos H 28-Apr-05

    This is what I have for the first 2 criteria:
    =COUNTIFS(Sheet1!D5:D4569,"RE",Sheet1!G5:G4569,"Pos")

    The numbers in 1st column are PINs not row numbers. I only want to COUNT a record if D=RE, G=Pos, and only once if there are multiples of these for each PIN.

    So looking at above, the result of the formula should be 7 = for these PINs: 980,988,992,997,1022,1028,1033.

    How do I add the 3rd criterion, count each PIN only once?

    Thank you!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: COUNTIFS formula help

    You could always use column H as a helper column with...

    =COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$E$2:E2,E2) >> drag down to end of data

    ...and then use

    =COUNTIF(H2:H4569,1)

    to sum up the 1's which are the first record of the PIN and the other criteria
    HTH
    Regards, Jeff

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: COUNTIFS formula help

    hi rjc1971, welcome to the forum. to count unique records, you probably need something more complicated like this array formula:
    =SUM(IF(FREQUENCY(IF($D$5:$D$4569="RE",IF($G$5:$G$4569="Pos",$C$5:$C$4569)),$C$5:$C$4569)>0,1))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    04-24-2013
    Location
    san diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: COUNTIFS formula help

    that did it...thank you!

+ 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. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  2. [SOLVED] Countifs with multiple criteria
    By yenaled in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-24-2013, 10:07 AM
  3. Replies: 0
    Last Post: 07-27-2011, 01:00 AM
  4. SUM COUNTIFS with multiple criteria
    By john_london in forum Excel General
    Replies: 4
    Last Post: 02-28-2011, 11:35 AM
  5. COUNTIFs with multiple criteria
    By Cene K in forum Excel General
    Replies: 5
    Last Post: 10-28-2005, 06:05 PM

Tags for this Thread

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