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

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.

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.