+ Reply to Thread
Results 1 to 6 of 6

Need formula to count multiple criteria in 2 columns

  1. #1
    Registered User
    Join Date
    08-08-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Need formula to count multiple criteria in 2 columns

    Hi,
    I have a set of data in column A and B. I need a formula to count the occurances of given cell contents in column 1 and in column 2.
    For example
    Column A Column B
    1c 1a
    p6 p7
    p5 p6
    p4 p8
    1b 1a
    P5 1c
    2c 2b

    I would like the formula to count all of the occurances of P4, P5, P6 in column A and those that are still P4, P5, P6 in column B. In the case of the data above the number returned will be 3 as the P5 second bottom is not a P number in Column B. Hope that makes sense!

    Any help would be gratefully received.
    Thanks
    Last edited by tancho321; 01-22-2013 at 04:51 PM.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need formula to count multiple criteria in 2 columns

    Based on your explanation there is only 1 instance that meets the conditions, row 3.

    Maybe you meant something like this...

    =SUMPRODUCT(--(LEFT(A2:A8)="P"),--(LEFT(B2:B8)="P"))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Need formula to count multiple criteria in 2 columns

    IF you want 4,5 or 6 only in Column A and any P value in B then try

    =SUMPRODUCT(--(A1:A7="P"&{4,5,6})* --(LEFT(B1:B7,1)="P"))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    08-08-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need formula to count multiple criteria in 2 columns

    Brilliant! That's perfect. Thank you for your prompt reply.

  5. #5
    Registered User
    Join Date
    08-08-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need formula to count multiple criteria in 2 columns

    Thank you both. Really appreciate your help.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need formula to count multiple criteria in 2 columns

    You're welcome. Thanks for the feedback!

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1