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

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
Asked:
txfan
  • 7
  • 3
1 Solution
 
barry houdiniCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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