Count number of rows with two matching parameters

Hello,

I have an excel workbook with many client account names (column A in example image).  There is a column B, which is irrelevant to this, but is the reason that client names are repeated.  
I need to then, in another workbook with just one entry for each client name, sum the number of times that the client name appears as true (1) in column C of the original workbook.  The cells with blue headers in the example image illustrate the 2nd workbook with just one entry for each account name.  This formula is important, as I have a very large number of clients, and need to perform this same statistic for a number of different criteria.

I am trying to do something along the lines of count the number of successful VLookups.   I have now gone this direction, for the data shown in the example image:  

=COUNT(IF('WookbookA.xls'!$A$2:$A$13="G2",IF('WorkbookA'!$C$2:$C$13=1,1,0),0))

The above formula would appear in cell H2.  In the real workbooks, the cells with yellow headers and blue headers are in separate workbooks.
excel-example-20111018.png
txfanAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Try SUMPRODUCT like this in H2 copied down

=SUMPRODUCT(('WookbookA.xls'!$A$2:$A$13=G2)*('WorkbookA'!$C$2:$C$13=1))

regards, barry
0
 
txfanAuthor Commented:
Barry,

It appears you are a genius.  I'm posting a visual illustration of your solution here for others' reference.   Thank you!
excel-example-20111018-solution.png
0
 
txfanAuthor Commented:
When I tried this same setup in the multiple-workbook situatio, I keep getting 0 results.  I will look over it and post back asap.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
txfanAuthor Commented:
Ok, it was my fault, Excel is (as usual) really picky about the formatting.  Barry's solution works great!  Thanks so much!
0
 
txfanAuthor Commented:
Great, full, simple, and concise answer.  Easy to follow and worked great.  Thanks!
0
 
barry houdiniCommented:
SUMPRODUCT works OK across workbooks - It can make a difference if the 1s are text rather than numbers -you can test which by using

=ISNUMBER(C2)

If you get TRUE then 1 should be OK, if that gives FALSE then you may need quotes around the 1 in the formula, i.e. "1"

regards, barry
0
 
txfanAuthor Commented:
I also want to add, for others' reference, that you can do this with more than two parameters.  Just adde another *(parameter) such as:

=SUMPRODUCT(('WookbookA.xls'!$A$2:$A$13=G2)*('WorkbookA'!$C$2:$C$13=1)*(WorkbookA'!$F$2:$F$13="pass"))
0
 
txfanAuthor Commented:
Barry,

If the "agreement" column (column C) has a value of 2.0, it is still counted just once.   How can I modify the formula to add "Two" to the sum?
Thanks!
0
 
barry houdiniCommented:
If it has 2.0 and you are counting 1s then I should think it won't be counted at all....If you want to sum column C based on column A then change to this

=SUMPRODUCT(('WookbookA.xls'!$A$2:$A$13=G2)+0,'WorkbookA'!$C$2:$C$13)

Note: usually for this type of formula you would use SUMIF but SUMIF doesn't work when the source workbook is closed so SUMPRODUCT is a good alternative

regards, barry
0
 
txfanAuthor Commented:
You are a genius.  Thank you Barry!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.