CODEWITHSUNDEEP
excelexcel-2007
user333217
user333217

Reputation: 7

Excel using a formula to check a condition and write the count

I have two columns Column M which has a set of details say server type and another column K which contains the alert types received. Now in the next sheet I want the result as check for the specific server type in column K and then to check for a specific alert and write the count of the number of times the a specific alert occurred.

Column K                 Column M
**Sub Component**        **Server Type**
Unknown                  Application Batch 1
CPU                      INC 4
Process                  ASSUMPTIVE 1
Filesystem               INFRA
Disk                     INFRA
FileSystem               INFRA
Unknown                  ASSUMPTIVE 1
FileSystem               ASSUMPTIVE 1

So the result am looking is:
In server Infra how many Filesystem alerts came (answer = 2)
In server Assumptive 1 how many Process alerts came (answer = 1)

So like this for all the servers I need to know the count of the individual alerts received

Upvotes: 0

Views: 60

Answers (3)

Ben Rhys-Lewis
Ben Rhys-Lewis

Reputation: 3246

Based on your edits and comments I would say you need something like:

=COUNTIF(a1:a10,"Filesystem")

That will tell you how many times Filesystem appears in that range. If you want it to look in another worksheet add 'worksheetname'! before the range.

=COUNTIF('sheet2'!a1:a10,"Filesystem")

EDIT - try this

=SUMPRODUCT((B1:B8="filesystem")*(C1:C8="INFRA"))

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36870

Use =Countifs() formula:

=COUNTIFS(B:B,D2,A:A,E2)

Screenshot
enter image description here

You can also use server type and alert type as criteria of =Countifs() formula. See below...

=COUNTIFS(B:B,"INFRA",A:A,"Filesystem")

But this will return count result only for INFRA server type and Filesystem alert type.

Upvotes: 1

Tam Coton
Tam Coton

Reputation: 864

If you want to pull out the number of one specific alert, look into COUNTIF. If you want to count how many of each alert there are, look into Pivot Tables.

Upvotes: 0

Related Questions