Count the frequency of a text value in an external workbook that meets criteria

[cono07]

Hi

I have been searching through the forums hoping to find a solution to my problem and although I have found some great tips on other excel problems, I can't seem to find the right solution to my problem:

I have a large worksheet named "Spring" in a workbook called "Objectives Tracker".
On this worksheet I have a list of user names in A2:A100 and named objectives (e.g. OB1, OB2, OB3, etc) in columns B to column F. If an objective is achieved, "A" or "B" or numbers 1 - 6 are entered depending on when it was achieved. Column G is headed 'Leaver'. If someone has left, "Y" is put in this cell otherwise it is "N".

In an external workbook I have a table where I want to show how many people achieved "A" for each of the objectives, how many achieved "B" and how many achieved anything, e,g, "A" or "B" or 2 or 3 etc. However I don't want to include leavers so column G must contain "N".
The table I'm filling looks like this:

OB1 OB2 OB3 OB4 OB5​

A
B
ANY

Starting with OB1, I have tried the following formula which did count the number of "A"s in column B but it didn't discount those with "Y" in column G:

{=IF('W:\Assessment\[Objectives Tracker.xlsx]Spring'!$G$2:$G$100="N",SUM(IF('W:\Assessment\[Objectives Tracker.xlsx]Spring'!B$2:B$100="A",1,0)))}

And to count specifically the number of "A"s and any numbers (1-6) I tried:

{=IF('W:\Assessment\[Objectives Tracker.xlsx]Spring'$G$2:$G$100="N",SUM(IF('W:\Assessment\[Objectives Tracker.xlsx]Spring'!B$2:B$100={1,2,3,4,5,6,"A"},1,0)))}

I think I'm probably using the wrong type of formula for this situation so any advice anyone can offer would be really helpful.

Thanks in advance

 

[Aby]

a sample file from you with dummy data will mean that we do not need to recreate everything from scratch..

 

[cono07]

Hi Aby

I have made a sample data file with source data and another sample workbook of the analysis. In reality the actual source worksheet has over 160 columns but this should hopefully give an idea of what I'm trying to achieve.

Essentially I am trying to use a sumif and countif combined as an array formula to work when my source data is an external workbook. (From what I understand, sumif and countif does not work with external closed workbooks)

The source data is named "Tracker" and the analysis sheet where the formulas will be placed is "Objective Count"

Thanks