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:

	A	B	C	D	E	F	G	H
1	Hostname	Object Class		Hostname	Object Class		Hostname	Object Class
2	TN1C1PF	Process		TN1C1PF	Network		TN1C1PF	Application
3	HPF	Process		HPF	Network		HPF	Application

<tbody>
</tbody>

But I also want to filter by Facility

Tucson

Birmingham

Abilene

Big Spring

Oro Valley

<tbody>
</tbody>

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:

Facility	Number of Alerts	Object Class
Tucson	x	x
Birmingham	x	x
Abilene	x	x
Big Spring	x	x
Oro Valley	x	x

<tbody>
</tbody>

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:

Facility	Number of Hostnames matching from dcounta filter
Alpine	X
Anna	X
Augusta	X

<tbody>
</tbody>

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