Filter with DCOUNTA and another column

[ntilton]

Hi,

I've been sifting through the forum and I can't find my specific situation, any help is greatly appreciated!

I want to use DCOUNTA to be able to make what I am filtering for apparent to the end user and because it seems to be less resource intensive than using countifs or sumproduct.

So I want to find data that matches:

For Example
=dcounta('November'!A2:G100, "Hostname", A1:A3)

A2:G100 contains the Data Table

Criteria:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Hostname[/TD]
[TD]Object Class[/TD]
[TD][/TD]
[TD]Hostname[/TD]
[TD]Object Class[/TD]
[TD][/TD]
[TD]Hostname[/TD]
[TD]Object Class[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TN1C1PF[/TD]
[TD]Process[/TD]
[TD][/TD]
[TD]TN1C1PF[/TD]
[TD]Network[/TD]
[TD][/TD]
[TD]TN1C1PF[/TD]
[TD]Application[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]HPF[/TD]
[TD]Process[/TD]
[TD][/TD]
[TD]HPF[/TD]
[TD]Network[/TD]
[TD][/TD]
[TD]HPF[/TD]
[TD]Application[/TD]
[/TR]
</tbody>[/TABLE]


But I also want to filter by Facility
[TABLE="width: 100"]
<tbody>[TR]
[TD]Tucson[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[/TR]
[TR]
[TD]Abilene[/TD]
[/TR]
[TR]
[TD]Big Spring[/TD]
[/TR]
[TR]
[TD]Oro Valley[/TD]
[/TR]
</tbody>[/TABLE]

So I want to find anything with a hostname of TN1C1PF or HPF with the respective 'Object Class' and at the respective Facility like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Facility[/TD]
[TD]Number of Alerts[/TD]
[TD]Object Class[/TD]
[/TR]
[TR]
[TD]Tucson[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Abilene[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Big Spring[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Oro Valley[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]


How can I combine the DCOUNTA and narrow it down by facility on each row?

Let me know if I need to provide more explanation

 

[Sam_D_Ben]

Need some more details like - how do we relate this facility to criteria data. If you don't mind.


Muz..

 

[ntilton]

Need some more details like - how do we relate this facility to criteria data. If you don't mind.


Muz..

Sure!

I want to know how many alerts happened at each Facility by hostname and object class - simple enough with a countifs or sumproduct formula BUT those are too resource intensive for the amount of data I'm dealing with.

a countifs would be something like =countifs(facilityDataTableRange, "Alpine", objectParameterRange, "Process", hostnameRange, "HPF")

which would give me a count of all alerts HPF process alerts at Alpine.

But that is too bulky and slow for the amount of data I have

So I have a column of Facilities:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Facility[/TD]
[TD]Number of Hostnames matching from dcounta filter[/TD]
[/TR]
[TR]
[TD]Alpine[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Augusta[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

and I want to run the DCOUNTA formula from before but then filter by facility

I can't include the facility in the DCOUNTA criteria as this would be an overload of data as I have over 130 facilities that I am breaking down.

The data table includes the following:
Facility, Arrival Date, Object Class, Hostname and many others

I want to break down by Arrival Date, Object Class and Hostname in the DCOUNTA criteria and also (and this is my question part) break down by facility by some other means I suppose, but I haven't got a good idea of how to do it.

THANKS!!!!

***EDIT - here's my formula:

=dcounta('Big November Data'!$A:$AH, "Hostname", criteriaRange)
and I want it to be something like:
=dcounta('Big November Data'!$A:$AH, "Hostname", criteriaRange AND Facility)

and put the formula like this:

Facility Alert Count
Alpine formula
Anna formula
Augusta formula