Count if criteria is met

[jamphan]

I have a table (Table 1) with several of the same branch numbers that appear many times and a Y or N field. What I am trying to do is if the branch number in Table 1 matches the branch number in Table 2 count the number of Y in the Y or N field from table 1. What would be the best formula to accompish this?

[sweep]

Hi,

Presuming for the sake of discussion, the branch number you want to look at is in E1, and the list of branch numbers is A1:A9, and the list of Y/N is in B1:B9, the following array will give you what you're after

=COUNT(IF(A1:A9=E1,IF(B1:B9="Y",1,""),""))

Remember to use CTRL, SHIFT and ENTER to confirm an array (it should display in the formula bar like this)

{=COUNT(IF(A1:A9=E1,IF(B1:B9="Y",1,""),""))}