[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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
0
svillardi
Asked:
svillardi
  • 5
  • 5
  • 3
1 Solution
 
barry houdiniCommented:
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
 
gowflowCommented:
here it is
use countifs
gowflow
patch-test.xlsx
0
 
svillardiAuthor 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gowflowCommented:
Your correct after seeing barry's formula and reading your post more carefully I have to admit I am totally offtrack !
gowflow
0
 
gowflowCommented:
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
 
barry houdiniCommented:
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
 
svillardiAuthor Commented:
Barry,

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

Thanks,

Scott.....
0
 
barry houdiniCommented:
yes you can filter by unique then copy elsewhere......

barry
0
 
svillardiAuthor Commented:
I decided to use a pivot table.  Thank you.
0
 
gowflowCommented:
Just a small comment, Barry gave you all this valuable info .... for nothing ?
gowflow
0
 
svillardiAuthor 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
 
svillardiAuthor Commented:
Thanks for the help.  I appreciate your time and effort.
0
 
gowflowCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now