Solved

# Excel counts for 2 or more columns

Posted on 2011-10-31
288 Views
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
Question by:svillardi

LVL 50

Expert Comment

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

LVL 29

Expert Comment

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

Author Comment

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

LVL 29

Expert Comment

gowflow
0

LVL 29

Expert Comment

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

LVL 50

Expert Comment

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 Comment

Barry,

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

Thanks,

Scott.....
0

LVL 50

Accepted Solution

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

barry
0

Author Comment

I decided to use a pivot table.  Thank you.
0

LVL 29

Expert Comment

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

Author Comment

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 Closing Comment

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

LVL 29

Expert Comment

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

### Suggested Solutions

Excel File Date Format 6 17
Min Month2 5 16
Create exported XLS from Query 19 13
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

#### Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!