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

2. ## 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. ## 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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