Excel count unique

0
votes

I have a spreadsheet with IPs, their owner, and their program and their vulnerabilities. So there are duplicate IPs.

I'm trying to write a formula to count unique IPs based on those their owner and program, so I don't have to do it manually for 6 owners and their 6 programs 2 a week. I've looked at sumproduct ans frequency and a few other sources but I can't figure out how to use them in this situation.

DATA SET

Owner - Program - Plugin - IP

Erica - Project 1 - 1234 - 1

Erica - Project 1 - 4321 - 1

Erica - Project 1 - 5421 - 2

Philip - Project 1 - 1237 - 2

Philip - Project 2 - 3289 - 3

Philip - Project 3 - 4321 - 4

Philip - Project 3 - 1234 - 5

Philip - Project 3 - 3245 - 6

Jacob - Project 3 - 1623 - 7

Jacob - Project 2 - 1234 - 8

OUTPUT

Owner - Program - IP

Erica - Project 1 - 2

Philip - Project 1 - 1

Philip - Project 2 - 1

Philip - Project 3 - 3

Jacob - Project 2 - 1

Jacob - Project 3 - 1
1
excelcountexcel-formulaunique
What have you tried? Index/Match? How is your data laid out, some mock data (and expected output) would help. – BruceWayne
I've tried SumProduct, Frequency and Match. But i'm probably not using them correctly. I don't know how to add columns on stackoverflow. Soo, it's laid out in columns in this order Owner, Program, Plugin, IP. However i'd like to get the number of unique IPs in each program/owner combination. – Captor
in Excel 2010+ might be a bit easier with Pivot Table Distinct Count stackoverflow.com/a/21552440/1383168 – Slai

1 Answers

0
votes

If the aim id to count the number of distinct IP's given the owner in column A and the Program in column B, you can use this formula in C2 and fill down:

=SUMPRODUCT(((Dataset!$A$2:$A$11=A2)*(Dataset!$B$2:$B$11=B2))/ COUNTIFS(
 Dataset!$A$2:$A$11, Dataset!$A$2:$A$11,
 Dataset!$B$2:$B$11, Dataset!$B$2:$B$11,
 Dataset!$D$2:$D$11, Dataset!$D$2:$D$11))