Count Unique Data Sets Over Multiple Columns

[AVO]

Hi,

I am struggling to create a formula that returns a singular count for data sets over 2 columns (that are multiple columns away from each other).

I want the formula to count something only if:

• It is only counting any number in Column A once.
• It is also only counting the unique number in column A if it can then select a specific piece of text in Column C (out of a string of text).

For the example below, I would like a return count of 3, with the example criteria (from the above dot points) being that there are 3 unique numbers in column A, that also have the word 'Good' contained in Column C.

I would also require this formula to factor in blank cells, and not count any blank cells in the selected column ranges!

Any help would be greatly appreciated.

I have tried PRODUCTSUMS and SUMS combined with COUNTIFS and IFS but cant quite get it to work...

Thank you.

 

[AVO]

Hi,

I am struggling to create a formula that returns a singular count for data sets over 2 columns (that are multiple columns away from each other).

I want the formula to count something only if:

• It is only counting any number in Column A once.
• It is also only counting the unique number in column A if it can then select a specific piece of text in Column C (out of a string of text).

For the example below, I would like a return count of 3, with the example criteria (from the above dot points) being that there are 3 unique numbers in column A, that also have the word 'Good' contained in Column C.

I would also require this formula to factor in blank cells, and not count any blank cells in the selected column ranges!

View attachment 92545

Any help would be greatly appreciated.

I have tried PRODUCTSUMS and SUMS combined with COUNTIFS and IFS but cant quite get it to work...

Thank you.

Update to the above (if it makes a difference), If an item appears once, its data will always be the same (ie: '1' will always have 'Very Good' in the other column)

 

[KRice]

Could you clarify your problem a little more, please?

if it can then select a specific piece of text in Column C (out of a string of text).

When you mention "specific piece of text" are you referring to a whole word, a phrase consisting of more than one word, a letter, multiple letters, etc.? I believe you are referring to a single word, but it's not clear.

Also, is there any relationship between the numbers in Column A and the text in Column C? For example, you mentioned that "1" in col A will always have "Very Good" in col C. Are there similar rules for other numbers?

Does column C always contain an item from a list of potential items, or are the possibilities for col C unlimited? If there is a standard list of col C items, what does it look like?

Do you want the reported count to be the maximum of potentially multiple unique combinations? For example, "rotten" appears for numbers 3 and 4, so would have a count of 2. Why is 2 not a legitimate answer?

 

[Peter_SSs]

For the example below, I would like a return count of 3, with the example criteria (from the above dot points) being that there are 3 unique numbers in column A, that also have the word 'Good' contained in Column C.

Give this a try

23 06 06.xlsm
	A	B	C	D	E	F
1	1		Very Good		Good	3
2					Rotten	2
3	3		Slightly Rotten
4	5		Kind of Good
5
6	1		Very Good
7	2		Good
8	4		Partly Rotten
9
10	3		Slightly Rotten
11
12	2		Good
Count AVO

Cell Formulas
Range	Formula
F1:F2	F1	=SUMPRODUCT(--ISNUMBER(SEARCH(E1,C$1:C$12)),--(MATCH(A$1:A$12&"|"&C$1:C$12,A$1:A$12&"|"&C$1:C$12,0)=ROW(A$1:A$12)-ROW(A$1)+1))

 

[Peter_SSs]

With the sample data below, the formula in my previous post would return 4 for "Good" because of the addition of row 2 data despite the word "Good" not appearing in that row.
If that sort of "word within a word" scenarion might be possible for you and you don't want to include the longer word in the count, then try this adaptation.

23 06 06.xlsm
	A	B	C	D	E	F
1	1		Very Good		Good	3
2	6		Extra Goodness		Rotten	2
3	3		Slightly Rotten
4	5		Kind of Good
5
6	1		Very Good
7	2		Good
8	4		Partly Rotten
9
10	3		Slightly Rotten
11
12	2		Good
Count AVO (2)

Cell Formulas
Range	Formula
F1:F2	F1	=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&C$1:C$12&" ")),--(MATCH(A$1:A$12&"|"&C$1:C$12,A$1:A$12&"|"&C$1:C$12,0)=ROW(A$1:A$12)-ROW(A$1)+1))