+ Reply to Thread
Results 1 to 10 of 10

COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many?

  1. #1
    Registered User
    Join Date
    01-10-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many?

    I'm trying to create a function that will count how many if one of three criteria are met in Column A and one criteria is also met in Column B.

    So essentially, column A holds 6 different criteria and Column B holds much more than that (it includes different products). I need to count for one product (column B) that is in either one of three phases of the life cycle (column A).

    I have scoured the web, but have come up empty-handed. Any help would be greatly appreciated.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    Try using COUNTIFS with this syntax

    =SUM(COUNTIFS(A:A,{"A","B","C"},B:B,"D"))
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-10-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    Yea, I just tried =SUM(COUNTIFS(Data!I2:I60,{"Coding","Code Review","Testing"},Data!H2:H60,"DW"))

    and I'm getting 0. I should be getting 4.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    That should work, doing the following

    Count rows where
    I2:I60 = "Coding" OR "Code Review" OR "Testing"
    AND
    H2:H60 = "DW"

    Can you post a sample book?

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    That formula works for me with test data - are you sure the cells in question exactly match those text values, no trailing/leading spaces or similar?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,730

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    Hard to evaluate without seeing a sample, but check for leading/trailing spaces maybe?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    01-10-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    I completely see what I did wrong. The "DW" criteria is only part of the full text of the cell. How do I have it look for cells that contain "DW" even if the rest of the text is different? Wildcard, essentially.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    Try

    =SUM(COUNTIFS(Data!I2:I60,{"Coding","Code Review","Testing"},Data!H2:H60,"*DW*"))

  9. #9
    Registered User
    Join Date
    01-10-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    Worked like a charm! Thanks so much for the help! (I'm an idiot)

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

    You're welcome.

    You're not an idiot, this is actually fairly advanced stuff.
    Definately not the standard functional use of countifs (wouldn't find it in the help files)

+ 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. Replies: 2
    Last Post: 10-31-2013, 01:27 PM
  2. Counting some of the cells in one column IF the cell in another column is true
    By Jeep326 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2013, 02:53 AM
  3. Replies: 1
    Last Post: 06-21-2012, 03:54 PM
  4. Replies: 4
    Last Post: 04-09-2011, 02:51 PM
  5. Replies: 10
    Last Post: 12-22-2010, 07:06 AM

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