How to count only specific values

**Naheed** · 06-21-2015, 08:59 PM

My spread sheets has many columns.
Column A id ID No
Cloumn B is dropdown list- Open, Closed, Monitored, further investigations, Duplicate Monitored

Cloumn C has Query Source- Like From Provider1, Service, Staff, Guest

Column M Has Query Type- Enquiry, Feedback, Concern, Appreciation.

I have to create a dash board- That will tell-

Count all Column B Except Duplicate Monitoring for each source

Second table -I have to prepare query type for Open, Closed, Monitored, further investigation except duplicate monitored.

I have work-sheet and dash-board on two separate worksheets.

I tried all formulas but it doesn't give correct data. All the time it is counting duplicate Monitored also. Please advise...
Any help - much appreciated.

**gak67** · 06-21-2015, 09:32 PM

Hi. It would be easier to answer you if you upload a sample file, with confidential data removed.

For the first part I would do something like:
=COUNTIF(Worksheet!B:B,[Source])

For the second I would do something like:
=COUNTIFS(Worksheet!B:B,"<>"&"Duplicate Monitored",Worksheet!M:M,[Query Type])

Replace the square brackets with references, or text in quotes as necessary.

**Naheed** · 06-21-2015, 09:47 PM

I have attached the sheet. The formula I used:=COUNTIFS('[Report on Incidents & Service Recovery v0.5.xlsx]REGISTER'!$M:$M,D$19,'[Report on Incidents & Service Recovery v0.5.xlsx]REGISTER'!$E:$E,$B20) But it is counting duplicate internal Monitoring also.

Excel Spreadasheet- query.png

**FDibbins** · 06-21-2015, 10:00 PM

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.