Excel lookup values and count based on ID

Question

I would like to know how to get excel to count a value in a column IF it detects a specific (pre-stated) ID within a row.

Specifically, I have two sheets. In workbook A, a person enters data on a video coding project. They list the ID, date, start time, end time, and a variety of variables they may have seen. For instance, in the "PRIMARY" column, a coder may type P, W, or A.

What I am looking for is a way for someone who has access to a master workbook (let's call it workbook B), where I need a formula which searches workbook A for a unique ID (i.e., is video d509 anywhere in the workbook under the video ID column), and if it is, go over to the column that has the PRIMARY label, and count every instance of a "P" when the video ID is listed as d509, then count every instance of a "W" for the video which corresponds to that ID, and then count every instance of an "A" for the video which corresponds to that ID.

I tried using a vlookup / count if function, but it did not work (see below):

=IF(VLOOKUP(B2,'[Coder1 Template.xlsx]ENTRY'!$B$2:$K$7100,7,FALSE),COUNTIF('[Coder1 Template.xlsx]ENTRY'!$H$2:$H$7100,"P"),0)

I also will need this to eventually use multiple crtieria. For instance, it will need to count ONLY the P for that column for that specific video for partner 1. And then it would have to do the same for partner 2.

Any ideas on the best way to achieve this?

I am unsure how to attach the workbook as a sample but here are some images of what a template with dummy data looks like:

Coder (data entry) workbook:Entry workbook Master (data count) workbook: Master workbook

*NOTE: Sorry, in the images where I wrote "sum" I meant "count". Essentially I want it to count the number of Ps that correspond to each video ID.

Answer 1

Contrary to what I wrote in my comment, it turns out that COUNTIFS is available in Excel 2010. You can use it to count the number of rows that has a given id in column B and a "P" in column H. If you enter "d509" in cell B2, the following formula will return 5 (assuming the data shown in your Entry Workbook picture.

=COUNTIFS('[Coder1 Template.xlsx]ENTRY'!$B$2:$B$7100,B2,'[Coder1 Template.xlsx]ENTRY'!$H$2:$H$7100,"P")

The COUNTIFS function counts the number if items that meet all the criteria listed.

Answer 2

Check whether you have a COUNTIFS function; if so, this is your best answer. You can specify a range and more than one criterion.

Failing that, you may be able to do this with an array formula.

=SUM( N( B2='[Coder1 Template.xlsx]ENTRY'!$B$2:$K$7100 ) * N('[Coder1 Template.xlsx]ENTRY'!$H$2:$H$7100 = "P" ) )

using Ctrl-Shift-Enter to enter the formula so that it shows up with curly brackets {...} around it (you do not type these). Array formulas are generally fairly fragile but they can do some remarkable things. Here the N() function gives TRUE -> 1 and FALSE -> 0 and we can multiply parallel tests and sum the cases where both are true across the respective arrays.