+ Reply to Thread
Results 1 to 8 of 8

COUNTIF multiple criteria?

  1. 04-23-2012, 10:53 PM #1
    JapanDave
    JapanDave is offline
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    COUNTIF multiple criteria?

    I am trying to count have many people have working in a 24h period. Each shift is represented by a letter of the alphabet. As an example , lets say there are 3 shifts. Early, Late and night shift.
    Early shift = A
    Late Shift = B
    Night Shift = C
    With this, I can count have many people worked in the 24 hour period by using a simple countif code to count each letter A,B,C.

    But, I need to count how many full time workers and part time workers there are as a second criteria.
    I tried this formula to count full time workers with no success. 

    Please Login or Register  to view this content.
    I have attached a dummy workbook.
    Any help would be appreciated.
    Attached Files Attached Files
    Last edited by JapanDave; 04-24-2012 at 07:48 AM.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files
    Reply With QuoteRegister To Reply
  2. 04-23-2012, 11:45 PM #2
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: COUNTIF multiple criteria?

    in C22: =SUMPRODUCT((B3:B20="Full")*(C3:C20={"a","b","c"}))

    the result will be (10) different from the 11 that you have indicated in your sheet, since C11 is blank.

    in C23: =SUMPRODUCT((B3:B20="Part")*(C3:C20={"a","b","c"}))
    Reply With QuoteRegister To Reply
  3. 04-24-2012, 07:27 AM #3
    JapanDave
    JapanDave is offline
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: COUNTIF multiple criteria?

    Thanks , that did the trick.
    Reply With QuoteRegister To Reply
  4. 04-24-2012, 07:35 AM #4
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: COUNTIF multiple criteria?

    It seems that if A, B and C are the only possibilities for column C and you want to count all 3 possibilities then you are merely counting how many cells are occupied so you can simply use:
    =COUNTIFS($B$3:$B$20,"Full",$C$3:$C$20,"*")
    and
    =COUNTIFS($B$3:$B$20,"Part",$C$3:$C$20,"*")
    Reply With QuoteRegister To Reply
  5. 04-24-2012, 07:49 AM #5
    JapanDave
    JapanDave is offline
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: COUNTIF multiple criteria?

    Cutter. That works too. Thanks.
    Reply With QuoteRegister To Reply
  6. 06-19-2012, 11:41 AM #6
    sanjeevi888
    sanjeevi888 is offline
    Registered User
    Join Date
    06-18-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: COUNTIF multiple criteria?

    Dear Friends,

    i am currently using this code any short formula...?

    Count P IN T S V R

    =COUNTIF(C9:AF9, "P")+COUNTIF(C9:AF9, "IN")+COUNTIF(C9:AF9, "T")+COUNTIF(C9:AF9, "S")+COUNTIF(C9:AF9, "V")+COUNTIF(C9:AF9, "R")


    Regards,

    Sanjeevi
    Reply With QuoteRegister To Reply
  7. 06-19-2012, 01:19 PM #7
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: COUNTIF multiple criteria?

    Sanjeevi,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  8. 06-20-2012, 10:56 AM #8
    sanjeevi888
    sanjeevi888 is offline
    Registered User
    Join Date
    06-18-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: COUNTIF multiple criteria?

    Ok.. arlu1201 Sorry for this..
    Last edited by sanjeevi888; 06-20-2012 at 11:04 AM.
    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)

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