• Status: Solved
• Priority: Medium
• Security: Public
• Views: 236

# 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
0
txfan
• 7
• 3
1 Solution

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

Author 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

Author 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

Author Commented:
Ok, it was my fault, Excel is (as usual) really picky about the formatting.  Barry's solution works great!  Thanks so much!
0

Author Commented:
Great, full, simple, and concise answer.  Easy to follow and worked great.  Thanks!
0

Commented:
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

Author 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

Author 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

Commented:
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

Author Commented:
You are a genius.  Thank you Barry!
0

## Featured Post

• 7
• 3
Tackle projects and never again get stuck behind a technical roadblock.