+ Reply to Thread
Results 1 to 3 of 3

HELP please! Countif(s)

  1. #1
    Registered User
    Join Date
    10-01-2018
    Location
    England
    MS-Off Ver
    Mac
    Posts
    2

    HELP please! Countif(s)

    Hi there.

    I currently have a set of raw data which I have successfully used the COUNTIFS function to create tables of totals of products owned. Changing products for fruit, I'll show you how:

    Example data
    User1 - Apple, Orange, Banana
    User2 - Apple, Grapes, Banana, Apple
    User3 - Apple

    All of the product (fruit) data is contained within one column, and needs to stay as such.

    The table which summarises totals, can show me, for example, how many Apple owners own a secondary product, using a formula like so: =COUNTIFS('Raw data'!B:B,"*Apple*",'Raw data'!B:B,"*Banana*")

    Apple owners' secondary products:
    Banana - 2
    Orange - 1
    Grapes - 1
    Apple - N/a


    I'd like to be able to replace that N/a with a figure for people who own an Apple, and also, own a second Apple.

    Which in my example data would be = 1

    Really struggling to find a way!! Please help

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: HELP please! Countif(s)

    Hello and welcome to the forum.

    Try something like this:

    =SUMPRODUCT(--((LEN(B$2:B$100)-LEN(SUBSTITUTE(B$2:B$100,"Apple","")))/LEN("Apple")>1))

    Make sure to change the range to fit your data.

  3. #3
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    655

    Re: HELP please! Countif(s)

    Hi,

    You can do this:

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    1
    User1 Apple, Orange, Banana Banana
    2
    2
    User2 Apple, Grapes, Banana, Apple Orange
    1
    3
    User3 Apple Grapes
    1
    4
    Apple
    1
    Sheet: Sheet88

    Excel 2016 (Windows) 64 bit
    E
    1
    =COUNTIF(B$1:B$3,"*, "&D1&"*")
    Sheet: Sheet88

    E1 formula copied down.

+ 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. Adding a COUNTIF function to an existing IF AND COUNTIF Formula...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2018, 10:09 AM
  2. Replies: 8
    Last Post: 05-27-2017, 07:04 AM
  3. Faster if/countif formula for flagging uniques - countif too slow
    By Speshul in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2015, 01:44 PM
  4. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM
  5. Adding a CountIF to a formula that is already Countif
    By Cmorgan in forum Excel General
    Replies: 4
    Last Post: 06-01-2011, 09:34 AM
  6. COUNTIF or not to COUNTIF on a range in another sheet
    By Ellie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-15-2005, 05:06 PM
  7. Countif w/ Multiple Criteria-How do I use countif
    By Patrick_KC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2005, 05:05 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