Be seen. Boost your question’s priority for more expert views and faster solutions

Hi Excel Gurus,

I get a weekly vulnerability report showing me what machines need patches. Often 1 patch will fix several vulns.

What I need is the total amount of patches needed per machine.

In the past, I have copied unique cells and used a countif to determine the counts of vulns, patches and machines which need patches. But that was for only the column I wanted to count.

Any help would be appreciated.

Thanks.

patch-test.xlsx

I get a weekly vulnerability report showing me what machines need patches. Often 1 patch will fix several vulns.

What I need is the total amount of patches needed per machine.

In the past, I have copied unique cells and used a countif to determine the counts of vulns, patches and machines which need patches. But that was for only the column I wanted to count.

Any help would be appreciated.

Thanks.

patch-test.xlsx

=SUM(IF(FREQUENCY(IF(A$2:A

confirmed with CTRL+SHIFT+ENTER and copied down

see attached

patch-test-barry.xlsx

Barry - I think this is it, but I am confused by the long formula. Can you explain it so that I can understand it a bit more please?

gowflow

=COUNTIFS(A:A,A2,C:C,C2)

would give you the total patch applied per machine per patch type and per machine. I had put in my inital formula the vul which seems you don't need.

In barry's formula you get the total of patches applied per machine.

=SUM(IF(FREQUENCY(IF(A$2:A

The first part of the FREQUENCY function is formed by this IF function

IF(A$2:A$10=F2,IF(C$2:C$10

so that will return the row number of the

{1;1;3;FALSE;FALSE;FALSE;F

The FALSEs are where the machine doesn't match in rows 5 to 10 but where there is a match in rows 2 to 4 then you get 1, 1 and 3 because C2:C4 = patch1, patch1 and patch2 so the row number (within the range, so row 2 is row 1 of the range) of the

Now the "bins" of the freqency function are simply 1,2,3,4,5,6,7,8,9 (returned by ROW(C$2:C$10)-ROW(C$2)+1) so when 1,1, and 3 are put into those bins the two 1s go in the same bin, i.e. FREQUENCy returns this array

{2;0;1;0;0;0;0;0;0;0}

and our whole formula is reduced to

=SUM(IF({2;0;1;0;0;0;0;0;0

that scores 1 for every non-zero number so the result is 2, effectively a count of different values, given a condition in another column

regards, barry

So, I always get confused when I solve the problem myself how to give out points. Since I pay for the service, I can give them all to him, no sweat.

gowflow

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

barry