Too many formulas, need a VBA solution for COUNTIFS

otherworlds

New Member
Joined
Aug 27, 2019
Messages
5
I have a data feed that contains roughly 3500 rows. Columns are servername, OS, and multiple columns of software names. The data contains the version of software installed on each server. For example as below:

ServernameOSSoftware1Software2Software3
server1Windows Server 20083.57.6.54542.3.44
server2Windows Server 20165.17.6.74.5
server3Windows Server 20083.47.5.55432.3.44
server4Windows Server 20124.27.6.74.5

<tbody>
</tbody>


I need to COUNTIFS the data based on whether the installed version of said software is compliant for the OS.

For example, for Windows Server 2008 the compliant versions of Software1 are 3.5 and 3.6. Any other software versions are non-compliant. I have made a table of compliant versions as below:

Software1
Windows Server 2008Windows Server 2008R2Windows Server 2012Windows Server 2012 R2
3.5etcetcetc
3.6etcetcetc

<tbody>
</tbody>


Bearing in mind there are maybe 20ish different pieces of software that i need to report on, please can anyone offer any advice on how to do this in VBA?

Thanks
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Welcome to the Board!

You can do it with formulas, for example:


Book1
ABCDEFGHIJKL
1ServernameOSSoftware1Software2Software3Software1
2server1Windows Server 20083.57.6.54542.3.44Installed on:2110
3server2Windows Server 20165.17.6.74.5# of compliant versions1110
4server3Windows Server 20083.47.5.55432.3.44Windows Server 2008Windows Server 2008R2Windows Server 2012Windows Server 2012 R2
5server4Windows Server 20124.27.6.74.53.543etc
6server5Windows Server 2008R24.156.63.64.14.2etc
Sheet4
Cell Formulas
RangeFormula
H2=COUNTIFS($B:$B,H4,INDEX($C:$E,0,MATCH($H1,$C1:$E1,0)),"<>")
H3{=SUM(COUNTIFS($B:$B,H4,INDEX($C:$E,0,MATCH($H1,$C1:$E1,0)),H5:H10))}
Press CTRL+SHIFT+ENTER to enter array formulas.


Just change the ranges as needed. If you still want a VBA solution, please specify where everything is on your sheets, and how you want the results to look.
 
Upvote 0
Hi Eric, thanks for this.

I'm looking at your first formula now and i can't see how it is checking the Software1 version ($C:$C) against the list of approved/compliant versions for the specific OS (H5:H6). Would you be as kind as to break it down for me?

Thanks,
 
Upvote 0
First of all the MATCH($H1,$C1:$E1,0) in both formulas is to find the right column to search for the software. Since we are looking for Software1 (H1), it finds and searches column C.

Now the H2 formula does not look for the compliant versions. It just looks for the OS in column B, and a non-empty value in column C. By this I determine that a given server has the OS we're looking at, and has the software installed, but we don't know yet if it's a compliant version.

The H3 formula checks for compliant versions. I probably should have given this version:

=SUMPRODUCT(COUNTIFS($B:$B,H4,INDEX($C:$E,0,MATCH($H1,$C1:$E1,0)),H5:H10))

since it does not require the Control+Shift+Enter. It's very similar to the H2 formula, it looks for the OS in column B, and in column C it looks for the first compliant version from H5. It saves this partial total in an internal array. It repeats the process with the same OS, and the next compliant version from H6. And again for H7, H8, H9, and H10, then finally sums up all the versions. Instead of H5:H6, I used H5:H10 to allow for additional versions.

Clearer?
 
Upvote 0
one more thing, what about finding the non-compliant versions. without simply subtracting the compliant from the total, is there a better way to formulate that?
 
Upvote 0
Sure, how to you want to identify them? The 2 basic ideas are to use Conditional Formatting to highlight non-compliant software, or you could make a list somewhere.
 
Upvote 0
what i mean is non-compliant versions of the software we're already looking at. so it'd be any version numbers not present in the table of compliant version numbers. it's easy enough if there's only one compliant version, but to be non-compliant it needs to not match ANY of the compliant version numbers.
 
Upvote 0
I may have misunderstood the question. If you just want a count of the non-compliant versions, then there's really no better way than just subtracting the number of compliant versions from the number that it's installed on. I could devise a formula to do that, but it would come down to the same thing.

Somehow I thought you wanted a list of all the servers with non-compliant software on them. That can be done, even with formulas, but it probably would be better as a VBA macro.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top