Count distinctive values based on multiple criteria - Excel 2013

[bwinchell]

Hello,
I am trying to count distinctive values based on multiple criteria. This is somewhat related to the formula that was worked on in http://www.mrexcel.com/forum/excel-questions/957247-concat-indirect-not-working-dynamic-formula.html

• I have multiple tabs in a workbook with names based on dates. (E.g. VM_Audit_10012015, VM_Audit_11012015, etc...)
• I have a formula that counts all records based on multiple criteria

=COUNTIFS(INDIRECT("VM_Audit_"&$A3&"!"&"$A$2:$A$2000"),"<>"&"",INDIRECT("VM_Audit_"&$A3&"!"&"$I$2:$I$2000"),"BC_ESXi_CLUST*")

• I have a formula that counts distinctive records

{=SUM(IF(INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000")<>"",1/COUNTIF(INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000"),INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000"))))}

• I am trying to count all distinctive values in column J, if they are not blank & the adjacent cell in column I = "BC_ESXi_CLUST01"
  • Basically combine the 2 formulas but cannot figure out the logic especially since one is an array formula

[TABLE="width: 500"]
<tbody>[TR]
[TD]column/row[/TD]
[TD]A[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]VM1[/TD]
[TD]BC_ESXi_CLUST01[/TD]
[TD]bcserver1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]VM2[/TD]
[TD]BC_ESXi_CLUST01[/TD]
[TD]bcserver2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]VM3[/TD]
[TD]BC_ESXi_CLUST02[/TD]
[TD]bcserver1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]VM1[/TD]
[TD]BC_ESXi_CLUST02[/TD]
[TD]bcserver3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]VM3[/TD]
[TD]BC_ESXi_CLUST01[/TD]
[TD]bcserver6[/TD]
[/TR]
</tbody>[/TABLE]


Any help is appreciated.
Thanks
B

 

[Domenic]

Try...

=SUM(IF(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000")="BC_ESXi_CLUST01",IF(INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")<>"",1/COUNTIFS(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),"BC_ESXi_CLUST01",INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000"),INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[bwinchell]

Try...

=SUM(IF(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000")="BC_ESXi_CLUST01",IF(INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")<>"",1/COUNTIFS(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),"BC_ESXi_CLUST01",INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000"),INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

One adjustment needed:
(my bad as I did put in the initial post that it needs to equal "BC_ESXi_CLUST01". In actuality, it needs to be "BC_ESXi_CLUST*")

The formula works great for the initial IF statement if the text value is an exact match:
=SUM(IF(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000")="BC_ESXi_CLUST01"

Since IF will not accept wildcards, I have to use SEARCH (from my reading). I tried to modify that section with (does not like my syntax):
=SUM(IF(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000")=(SEARCH("BC_ESXi_CLUST*",(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000")))),



Am I on the right track or way off?
Thanks
B

 

[Domenic]

Try...

=SUM(IF(ISNUMBER(SEARCH("BC_ESXi_CLUST01",INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"))),IF(INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")<>"",1/COUNTIFS(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),"*BC_ESXi_CLUST01*",INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000"),INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")))))

Alternatively, since the text string starts with "BC_ESXi_CLUST01", the LEFT function can be used instead of SEARCH...

=SUM(IF(LEFT(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),15)="BC_ESXi_CLUST01",IF(INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")<>"",1/COUNTIFS(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),"BC_ESXi_CLUST01*",INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000"),INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")))))

And, of course, both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

 

[bwinchell]

Try...

=SUM(IF(ISNUMBER(SEARCH("BC_ESXi_CLUST01",INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"))),IF(INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")<>"",1/COUNTIFS(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),"*BC_ESXi_CLUST01*",INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000"),INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")))))

Alternatively, since the text string starts with "BC_ESXi_CLUST01", the LEFT function can be used instead of SEARCH...

=SUM(IF(LEFT(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),15)="BC_ESXi_CLUST01",IF(INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")<>"",1/COUNTIFS(INDIRECT("'VM_Audit_"&$A3&"'!I2:I2000"),"BC_ESXi_CLUST01*",INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000"),INDIRECT("'VM_Audit_"&$A3&"'!J2:J2000")))))

And, of course, both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

I used the second formula and it works great.

Thanks for the quick assistance.
B

 

[bwinchell]

Ok. The boss has changed his mind what information he wants out of this report so now things are changing

I have figured it is easier to actually use a pivot table. Since I now have to use a pivot table, it is a lot easier to put all my sheets into one sheet/table. So that is what I have done.
I have changed the formulas (which are easier since they always reference the same table...no need for dynamic formulas).

The one formula I am having an issue with is the one above. It is doing essentially the same thing but an additional variable needs to be in place.
The formula needs to now count all the cells that equal the Cluster name (J=BCV_ESXi_CLUST*) and match a date (A=MM/DD/YYYY), then count the distinctive values in host (K=hosts).
(The "A" column on this sheet just references the date range....A17=12/01/2015...in date format)
The formula I currently have is giving me an #DIV/0! error:

{=SUM(IF((tbl_MASTER!A$2:A4625=A16),1/COUNTIFS(tbl_MASTER!K$2:K4625,"<>"&"",tbl_MASTER!A$2:A4625,A17,tbl_MASTER!J$2:J4625,"BCV_ESXi_CLUST*",tbl_MASTER!K$2:K4625,tbl_MASTER!K$2:K4625)))}

This is what the table looks like now:
[TABLE="width: 750"]
<tbody>[TR]
[TD]A-date[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J-cluster[/TD]
[TD]K-host[/TD]
[/TR]
[TR]
[TD]10/01/2015[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]BCV_ESXi_Clust01[/TD]
[TD]bcvhost1[/TD]
[/TR]
[TR]
[TD]10/01/2015[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]BCV_ESXi_Clust02[/TD]
[TD]bcvhost4[/TD]
[/TR]
[TR]
[TD]12/01/2015[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]BCV_ESXi_Clust01[/TD]
[TD]bcvhost2[/TD]
[/TR]
[TR]
[TD]12/01/2015[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]BUR_ESXi_Clust02[/TD]
[TD]burhost3[/TD]
[/TR]
[TR]
[TD]12/01/2015[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]BUR_ESXi_Clust01[/TD]
[TD]burhost1[/TD]
[/TR]
[TR]
[TD]01/01/2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]BCV_ESXi_Clust01[/TD]
[TD]bcvhost2[/TD]
[/TR]
[TR]
[TD]01/01/2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]BCV_ESXi_Clust03[/TD]
[TD]bcvhost5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Domenic]

Part of the problem is that you're referencing two different cells for the date criteria (ie. A16 and A17). Assuming that A17 contains the date of interest, try...

=SUM(IF(tbl_MASTER!$A$2:$A$4625=A17,IF(LEFT(tbl_MASTER!$J$2:$J$4625,14)="BCV_ESXi_CLUST",IF(tbl_MASTER!$K$2:$K$4625<>"",1/COUNTIFS(tbl_MASTER!$A$2:$A$4625,A17,tbl_MASTER!$J$2:$J$4625,"BCV_ESXi_CLUST*",tbl_MASTER!$K$2:$K$4625,tbl_MASTER!$K$2:$K$4625)))))

...confirmed with CONTROL+SHIFT+ENTER.

 

[bwinchell]

Part of the problem is that you're referencing two different cells for the date criteria (ie. A16 and A17). Assuming that A17 contains the date of interest, try...

=SUM(IF(tbl_MASTER!$A$2:$A$4625=A17,IF(LEFT(tbl_MASTER!$J$2:$J$4625,14)="BCV_ESXi_CLUST",IF(tbl_MASTER!$K$2:$K$4625<>"",1/COUNTIFS(tbl_MASTER!$A$2:$A$4625,A17,tbl_MASTER!$J$2:$J$4625,"BCV_ESXi_CLUST*",tbl_MASTER!$K$2:$K$4625,tbl_MASTER!$K$2:$K$4625)))))

...confirmed with CONTROL+SHIFT+ENTER.

Thanks. That worked and good catch (been starring at these formulas for too long).