Table & CountIF help

[scottydoouk]

Hi Guys

I am after some advice please? I am currently working on a tracker for Audit scores with 4 possible outcomes. I have set the tables up in separate worksheets. I have then setup a processed sheet that will have the persons Name, Month audit completed and the number of audits in each category.

HA Name

Month

Case ID

Audit Type

1.1

1.2

1.3

2.1

2.2

3.1

3.2

4.1

4.2

5.1

5.2

5.3

6.1

6.2

7.1

8.1

8.2

8.3

Outcome

Shown above is the headings in the main sheet, the bottom is the setup I have for the counting

HA Name

Month

NA

SWL

S&A

100%

What I want is a formula to look at the month & HA Name, the for January (as an example) it auto populate to how many NA/SWL/S&A/100% that person has achieved for that month. Ideally, I would like to go a bit further and create another table so that it will count the partials and not achieved in each of the competencies in the top table headers.

Can anyone help please?

Eventually this will be pivot tabled so I can select the advisors name and it bring up their performance and audit statistics in a dashboard

Thanks in advance

 

[scottydoouk]

I was thinking this may solve it but I get an error:

COUNTIFS(('Audit Performance'!B2:B500=AD2,'Audit Performance'!A2:A500=[@[HA Name]],'Audit Performance'!W2:W500,"NA"))

 

[Sufiyan97]

What error you are getting
have you tried removing extra brackets?

=COUNTIFS('Audit Performance'!B2:B500=AD2,'Audit Performance'!A2:A500=[@[HA Name]],'Audit Performance'!W2:W500,"NA")

 

[Fluff]

You also need to remove the = sign from within the formula

=COUNTIFS('Audit Performance'!B2:B500,AD2,'Audit Performance'!A2:A500,[@[HA Name]],'Audit Performance'!W2:W500,"NA")