0

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

Improve this question
3
  • Hard to say from your description but I would try using COUNTIF Commented Feb 5, 2016 at 14:28
  • can you advise how to use, i dont need a pivot Commented Feb 5, 2016 at 14:36
  • all i need is first check for a name in a particular column and then in another column write the count of each alerts received Commented Feb 5, 2016 at 14:36

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

As it will check more than 1 condition, so you have to use =Countifs() formula.
For eg the Sub component and Server type is in another sheet how to get the anser in a separate sheet
Suppose your results are in sheet2 and data are on sheet1. Then put the formula in Sheet2 and refer Sheet1 in formula like =COUNTIFS(Sheet1!B:B,D2,Sheet1!A:A,E2)
I already told you that earlier. This guy wants it all on plate!
Mark it as correct then. Although this answer is also worthy :)
0

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.

Improve this answer

4 Comments

without Pivot i need the data
Anyone can answer with formuls
all i need is first check for a name in a particular column and then in another column write the count of each alerts received
...this is literally what pivot tables are for. This exact situation. This is what they do.
0

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"))
Improve this answer

6 Comments

it should also check the server name first and in that it should return the count
For eg check the server type and then find for a specifc alerts count
Serer type No of Fatal alerts No of Unknown alerts count Infra 3 1 Citrix-33 8 0
OK try using SUMPRODUCT then. Ive edited my answer. This gives number of INFRA that appear in column c when filesystem is in column B.
Hi This works in a separate one but i have the data in one sheet and the result needed in another sheet
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.