CODEWITHSUNDEEP
excel
Motu61
Motu61

Reputation: 17

Count number of unique values in a column, restricted by two other columns' criteria

I have a rather large data dump used in my analysis.

Here is a small example of a part of the data I am working with.

Example

What I am trying to is count how many unique serial numbers there are for a specific location (six flags) for a specific model (4250).

I was originally trying to apply a SUMPRODUCT divided by a COUNTIF statement that I found from another example with no success. I have found plenty of documentation on how to calculate frequencies, but nothing on having restrictions from two other column values.

Upvotes: 0

Views: 86

Answers (2)

user4039065
user4039065

Reputation:

Try this SUMPRODUCT/COUNTIFS formula.

=SUMPRODUCT(((A$2:A$17=E2)*(B$2:B$17=F2))/(COUNTIFS(C$2:C$17, C$2:C$17, A$2:A$17, E2, B$2:B$17, F2)+(A$2:A$17<>E2)+(B$2:B$17<>F2)))

enter image description here

The ranges should be kept to the minimum number of rows of actual data. To make these ranges dynamic you need to change all occurrences of A2:A17, B2:B17 & C2:C17 to,

'A2:A17
A$2:index(a:a, match("zzz", a:a))
'B2:B17
B$2:index(b:b, match("zzz", a:a))
'C2:C17
C$2:index(c:c, match("zzz", a:a))

Upvotes: 1

user9425644
user9425644

Reputation:

This is a conditional sum on concatenated contents of your desired criteria, where it only shows the answer for the first instance of the value. Hope this helps!

SFCF

Upvotes: 1

Related Questions