• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Cells in a Separate Spreadsheet

D

Douglas Eckert

New Member
Hello. I need to count matching cells in another spreadsheet. If the three-digit clinic designators (e.g., "251") match, I want to count the adjacent column ("Employed Now"), which displays "YES"" or "NO". In other words, if the clinic designators next to the doctors' names match, I need to count the number of "YES" answers next to their names. (This is similar to combining functions VLOOKUP and COUNTIF). Suggestions are welcome.

Doug in York PA
 
If I understand you correctly, this should work

=SUMPRODUCT((Sheet2!A1:A100=Sheet1!A2)*(Sheet2!B1:B100="Yes"))

Where the first column in Sheet1 is the designators, and the first and second columns of Sheet2 are the designators and the Yes/No columns respectively
 
Sam: In Sheet 2, I am trying to capture instances where the designators match AND the nearby Column C says "YES". This will produce a count in Sheet 1 of the doctors who still work for our company. In other words, I am trying to calculate and capture the subtotals of doctors who work at each particular clinic in Sheet 2, where Column C says "YES", and have the count populate a column in Sheet 1.

Doug
 

Attachments

  • Doug\'s Example 5Sep13.xlsx
    13.3 KB · Views: 2
I thought that's exactly what I proposed... only since I didn't know the sheet names and exact position of the columns, I posted the above formula

Try this

F3=SUMPRODUCT(('QTR 1 - Bonus Calc'!$A$2:$A$25='Mthly Accr Sum'!A3)*('QTR 1 - Bonus Calc'!$C$2:$C$25="YES")) and drag down
 
That was perfect - thank you, Sam Mathai Chacko!
Q: How does this work? Why did you use SUMPRODUCT instead of COUNTIF or VLOOKUP?

Doug in York PA
 
actually, we could have used =COUNTIF('QTR 1 - Bonus Calc'!A2:A25,'Mthly Accr Sum'!A3)

However, you said you only wanted to count those that had a YES in the adjacent column. And countif doesn't allow that.

Another formula we could have used is COUNTIFS, but I thought SUMPRODUCT would be compatible with versions of Excel prior to 2007, and hence the suggestion.
 
Sam: Thanks. I also need to sum a column of bonuses using a similar formula. How would I sum the bonuses in dollars from Column X, based on matching the two fields labeled A?

Doug
 
Here is what I tried to do: =SUM(('QTR 1 - Bonus Calc'!$A$3:$A$164='Mthly Accr Sum'!A11)*('QTR 1 - Bonus Calc'!$X$3:$X$164))

But, it only returns a number for one row instead of a range associated with a clinic.

Doug
 
Use SUMPRODUCT instead

OR (typing on the fly here, untested)

=SUMIF('QTR 1 - Bonus Calc'!$A$3:$A$164,'Mthly Accr Sum'!A11,'QTR 1 - Bonus Calc'!$X$3:$X$164)
 
You must log in or register to reply here.
Back
Top