+ Reply to Thread
Results 1 to 10 of 10

Countif formula to match multiple criteria

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Countif formula to match multiple criteria

    please see the attached workbook.

    on sheet 2 column C, i would like the formula to match the date in column A and the employee number in column B to the date and employee number on sheet 1 AND...
    count the amount of times the conditions match the column header.

    so for example, in cell C2 on sheet 2, i would like the amount of times employee number 1 on the 17/08/2013 had 1stfloor; bad; fast; and summer listed across a row.
    in cell C3, id like the same thing but for employee 2.

    columns D,E, and F do not need to be done. i put them in to help give an idea of what im doing.



    thank you!!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: complicated countif formula

    In C2, enter =SUM(IF(Sheet1!$A$2:$A$46=$A2,IF(Sheet1!$B$2:$B$46=$B2,IF(Sheet1!$C$2:$C$46&Sheet1!$D$2:$D$46&Sheet1!$E$2:$E$46&Sheet1!$F$2:$F$46=SUBSTITUTE(C$1,"-",""),1,0)))) with Ctrl + Shift + Enter keys together
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: complicated countif formula

    i was hoping the formula would contain the words "fast" and "winter" etc.
    the reason being that due to very senesitive nature of my work, i just created a simple example workbook but obviously would like to transfer the formula you provide to my real workbook.

    is there a way to actually use the words in the formula?

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: complicated countif formula

    I used the following formula and copied it down and over.

    =SUMPRODUCT((Sheet1!$A$2:$A$46=Sheet2!$A5)*(Sheet1!$B$2:$B$46=Sheet2!$B5)*(Sheet1!$C$2:$C$46="1stfloor")*(Sheet1!$D$2:$D$46="bad")*(Sheet1!$E$2:$E$46="fast")*(Sheet1!$F$2:$F$46="summer"))

    In my attached example, the references to the words in C1:C4 are still in place.

    sumprods.xlsx
    Last edited by daffodil11; 09-10-2013 at 06:20 PM. Reason: References converted to words

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

    Re: complicated countif formula

    Since you are using 2007+, this should work. If you want the words in the formula you are going to have to adjust for the other columns.

    C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    HTH
    Regards, Jeff

  6. #6
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: complicated countif formula

    im assuming there is no need for array brackets.

    also, because i dont know how many dates there will be in the future, sheet 1 can continue to grow beyond row 46. ive tried replacing $A$2:$A$46 with just A:A but i get a N/A error.
    what can i do to fix this

    thanks so much

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

    Re: complicated countif formula

    Correct, this is not an array formula so no brackets necessary.

    I would advise against using entire columns references; instead, just extend the range to something much farther than what you need...

    Maybe... $A$2:$A$500

    Also, you are most likely getting the error because you must adjust all ranges to be the same size.

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: complicated countif formula

    Jeffrey's suggestion is superior to mine, you could just use a much larger number on the range.

    =COUNTIFS(Sheet1!$A$2:$A$9999,$A2,
    Sheet1!$B$2:$B$9999,$B2,
    Sheet1!$C$2:$C$9999,"1stfloor",
    Sheet1!$D$2:$D$9999,"bad",
    Sheet1!$E$2:$E$9999,"fast",
    Sheet1!$F$2:$F$9999,"summer")

  9. #9
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Countif formula to match multiple criteria

    perfect.
    thanks guys

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

    Re: Countif formula to match multiple criteria

    You are very welcome. We are glad to have helped.

+ 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] Formula for multiple COUNTIF criteria
    By LuckyStrike in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2013, 12:06 PM
  2. [SOLVED] Multiple criteria for a countif formula please!
    By mcain1981 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2013, 05:07 PM
  3. COUNTIF formula for multiple Criteria
    By AbrahamRunning in forum Excel General
    Replies: 8
    Last Post: 02-06-2012, 09:29 AM
  4. COUNTIF formula for multiple Criteria
    By AbrahamRunning in forum Excel General
    Replies: 1
    Last Post: 01-30-2012, 03:29 AM
  5. Countif formula with multiple criteria ie >30 and <60?
    By Dali in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2005, 01: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