Counting # of specific values in one column for each unique IP in another column

Good day,

I am new to this forum and I am not that good with excel. However, I know that it is powerful and i'm pretty sure that with the help of the creative genius on this forum, I should be able to obtain the formulas that i need. Here is my setup:

Column A can have one of 4 keywords including (critcal, high, medium, and low)
Column B has an IP address with matching octets for the first 3 octets e.g. 192.168.1.* in a range from 192.168.1.1 - .254

I am trying to count each occurence of the 4 keywords for each unique ip so that i can build this type of report

e.g.
# of IP's with "critical" = 10
# of IP's with "high" = 40
# of IP's with "medium" = 30
# of IP's with "low" = 50

What is the simplest way for me to do this. I appreciate any help. Thanks in advance.

You get better help, with an excel example without confidentional information.

Please also add the desired (expected) result.

I guess it can be done with an helpcolumn (and after that an pivot table).

If you show us the file, I will take a look at it.

Please add the file, so i can work in it.

So long as the first three octets are always the same length (11 characters long in this case) you can do this...

Add a column C and put the formula =LEFT(B2,11) and copy it down.

Now for the counts you want use this array formula**
=SUM(IF(FREQUENCY(IF(A2:A300="critical",IF(C2:C300<>"",MATCH(C2:C300,C2:C300,0))),ROW(C2:C300)-ROW(C2)+1),1))
For the part of the above formula in red, change the word for each status.

**When entering an array formula you must use Ctrl+Shift+Enter otherwise it won't work.

With helpcolumn and an pivot table.

See the attached file.

I delete a lot off data, otherwise the file is the big to post.

The formula in the sheet you can copy to your Original file.

You realy use excel 2003 (and not 2007 or further)?

It make the use of pivot table a lot easier (to use / to explain)

@ Light C - The formula has to be adjusted per what you're looking at. Take a look at the attached spreadsheet.
Light C solution.xlsx
FYI, the attached spreadsheet contains an extra record I put in. be sure to apply the solution to a fresh dataset


@oeldere - sorry to call you out on this, but the solution you gave does not give an accurate count based on Host IDs provided

If you keep the sheet I gave you as your form, then yes. When you update the formulas with new ranges, you MUST use Ctrl+Shift+Enter. Otherwise they won't work. You have to do this because they are array formulas.

@Craig K.

Please Login or Register  to view this content.

Please will you tell me, where you think it goes wrong.

Then I will take a look at it.

@Oeldere - Based on what Light C was asking for, the pivot you created did not count for unique instances of IP Address values. In order to do that with your pivot table you would need to put inside the Row Labels: (1)Risk, (2)helpcolumn. Then you would need to count the number of values inside each grouping. I hope that makes sense.

@Craig K.

Thanks for the reply.

Probably I understand what your trying to say.

In column E you see (indeed) the unique numbers going from 1 to 2 etc.

But in my pivot table I only selected the 1 values, so the result should be okay.

Hope this will help to understand the solution.

@oeldere - Your solution is correct. I got it in my head that something looked off and I was trying to ratinalize it. I apologise.

You don't have to apologise.

I glad I cleared the air blue for you.

@Craig K.

In that case you like the solution in the attached file more.

Thanks for the reply.

Glad I could help.