# Excel counts for 2 or more columns

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
###### Who is Participating?

Commented:
yes you can filter by unique then copy elsewhere......

barry
0

Commented:
If you can get a list of machines in F2 down then you can use this formula in G2

=SUM(IF(FREQUENCY(IF(A\$2:A\$10=F2,IF(C\$2:C\$10<>"",MATCH(C\$2:C\$10,C\$2:C\$10,0))),ROW(C\$2:C\$10)-ROW(C\$2)+1),1))

confirmed with CTRL+SHIFT+ENTER and copied down

see attached
patch-test-barry.xlsx
0

Commented:
here it is
use countifs
gowflow
patch-test.xlsx
0

Author Commented:
Gowflow - appreciate the response, but this isn't what I am looking for.  For each individual machine, I need the total amount of patches the machine needs.

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?

0

Commented:
Your correct after seeing barry's formula and reading your post more carefully I have to admit I am totally offtrack !
gowflow
0

Commented:
to be more accurate this
=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.
0

Commented:
It's not a simple formula but a standard one for counting different values in one column based on a criteria in another column.

=SUM(IF(FREQUENCY(IF(A\$2:A\$10=F2,IF(C\$2:C\$10<>"",MATCH(C\$2:C\$10,C\$2:C\$10,0))),ROW(C\$2:C\$10)-ROW(C\$2)+1),1))

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

IF(A\$2:A\$10=F2,IF(C\$2:C\$10<>"",MATCH(C\$2:C\$10,C\$2:C\$10,0)))

so that will return the row number of the first appearance of each C2:C10 value (assuming A2:A10 meets the criteria, i.e. matches that machine, and that C2:C10 is not blank), so in your example that IF function returns this array in G2

{1;1;3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

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 first occurrence

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;0},1))

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

0

Author Commented:
Barry,

How would I get the column of machines?  Just copy unique?

Thanks,

Scott.....
0

Author Commented:
I decided to use a pivot table.  Thank you.
0

Commented:
Just a small comment, Barry gave you all this valuable info .... for nothing ?
gowflow
0

Author Commented:
Unfortunately, I couldn't use his information... but I do get your point.

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.

0

Author Commented:
Thanks for the help.  I appreciate your time and effort.
0

Commented:
My cpmment was not for point but to ackgnowlege effort. Then for points I guess you decide what ever you wish to do and if Exprt do not agree with your decision then they can object and moderators will comein to judge what is / what is not

gowflow
0
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.