Too many formulas, need a VBA solution for COUNTIFS

[otherworlds]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Servername[/TD]
[TD]OS[/TD]
[TD]Software1[/TD]
[TD]Software2[/TD]
[TD]Software3[/TD]
[/TR]
[TR]
[TD]server1[/TD]
[TD]Windows Server 2008[/TD]
[TD]3.5[/TD]
[TD]7.6.5454[/TD]
[TD]2.3.44[/TD]
[/TR]
[TR]
[TD]server2[/TD]
[TD]Windows Server 2016[/TD]
[TD]5.1[/TD]
[TD]7.6.7[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]server3[/TD]
[TD]Windows Server 2008[/TD]
[TD]3.4[/TD]
[TD]7.5.5543[/TD]
[TD]2.3.44[/TD]
[/TR]
[TR]
[TD]server4[/TD]
[TD]Windows Server 2012[/TD]
[TD]4.2[/TD]
[TD]7.6.7[/TD]
[TD]4.5[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Software1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Windows Server 2008[/TD]
[TD]Windows Server 2008R2[/TD]
[TD]Windows Server 2012[/TD]
[TD]Windows Server 2012 R2[/TD]
[/TR]
[TR]
[TD]3.5[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]3.6[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Eric W]

Welcome to the Board!

You can do it with formulas, for example:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Servername	OS	Software1	Software2	Software3			Software1
2	server1	Windows Server 2008	3.5	7.6.5454	2.3.44		Installed on:	2	1	1	0
3	server2	Windows Server 2016	5.1	7.6.7	4.5		# of compliant versions	1	1	1	0
4	server3	Windows Server 2008	3.4	7.5.5543	2.3.44			Windows Server 2008	Windows Server 2008R2	Windows Server 2012	Windows Server 2012 R2
5	server4	Windows Server 2012	4.2	7.6.7	4.5			3.5	4	3	etc
6	server5	Windows Server 2008R2	4.1	5	6.6			3.6	4.1	4.2	etc
Sheet4

Cell Formulas
Range	Formula
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.

 

[otherworlds]

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,

 

[Eric W]

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?

 

[otherworlds]

yeah, that's great. thanks very much.

 

[otherworlds]

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?

 

[Eric W]

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.

 

[otherworlds]

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.

 

[Eric W]

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.