1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007: Can I use multiple criteria for Countif?

Discussion in 'Business Applications' started by noisette, Jan 16, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. noisette

    noisette Thread Starter

    Joined:
    May 12, 2009
    Messages:
    10
    Hi! I tried using Countif but without any luck and am not sure what will actually. I need Excel to look up data in Column E, count any instances of "Tree" and then look in Columns K to M any count instances of "c" where E = "Tree". For example:

    Column E
    Tree
    Plant
    Flower
    Tree
    Tree

    Column K
    c
    c
    b
    b
    g

    Column L
    b
    b
    g
    b
    g

    Column M
    b
    b
    c
    b
    c

    I would be looking for Excel to return "2" as there are two Trees in Column E that have a 'value' of c in columns K to M. Am I missing an obvious solution here?

    Many thanks as always for any advice!
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,251
    First Name:
    Wayne
    this should work
    =SUMPRODUCT(($E$1:$E$100="tree")*($K$1:$O$100="c"))
    OR

    =COUNT(IF((E1:E100="tree")*(K1:O100="c"),1))
    as an array formula - after adding to the formula bar - use contol+shift+enter to change to an array formula } will appear around the formula

    see attached spreadsheet
    with both formulas in D14 and D16

    EDIT - while working on the solution, you edited the post - so my data does not match yours - the answers the same though
     

    Attached Files:

  3. noisette

    noisette Thread Starter

    Joined:
    May 12, 2009
    Messages:
    10
    Thank you so, so much! That's great. I really appreciate this.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,251
    First Name:
    Wayne
    your welcome (y) thanks for letting us know
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085473

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice