How to count the row if criteria for 2 columns match

[mcjames]

I'm working on a spreadsheet that records the customer's name, acct#, the employee helping them, payments made the day of service, payments received for an acct, and the payment method. On the summary page, I am trying to break down the payments at time of service by employee. I’m currently using the “COUNT” formula, which is calculating how many times the employee's initials show up, but not specifically the “payments made the day of service.”

This is an example of my spreadsheet. I'm only displaying the columns I need the formula to pull from.

Column B Column F Column G
employee day of service received on acct
BK 100.00 x
BK 200.00 x
BK x 150.00
NG 50.00
NG x 50.00
PK x 150.00
DT 100.00
DD x 125.00
DD 150.00

So with this example, my results should tally:

BK=2
NG=1
PK= 0
DT=1
DD=1

I'm pretty sure I need to be using the "COUNT" function, but just can't figure out how to enter the formula. Also, Column B, with the employee's initials is a drop down list. Not sure if it makes a difference or not.

 

[jacqueslegrand]

If I understand you well, you would have to do, for BK, for instance :
=sumproduct((F1:F9<>"x")*(B1:B9="BK"))

 

[kevatarvind]

try below for BK

=SUMPRODUCT((F1:F9<>"x")*(B1:B9="BK"))


or if you using excel 2007 or later then below one can be same

=COUNTIFS(B:B,"BK",F:F,"<>X")

 

[mcjames]

the "X" listed in my example is actually going to be a blank cell, I just didn't know how else to show that in this message box. Do I still enter "X" for the blank spots? or will there have to be a value for the formula to work?

 

[mcjames]

the "X" listed in my example is actually going to be a blank cell, I just didn't know how else to show that in this message box. Do I still enter "X" for the blank spots? or will there have to be a value for the formula to work?

 

[kevatarvind]

Try below
=SUMPRODUCT((F:F<>"")*(B:B="BK"))

OR

=countifs(b:b,"bk")-countifs(b:b,"bk",f:f,"")

 

[mcjames]

thank you thank you thank you! SUMPRODUCT works perfectly!

 

[kevatarvind]

thank you thank you thank you! SUMPRODUCT works perfectly!

Thanks For The Feedback

Your Welcome
Glad To Help