CODEWITHSUNDEEP
excelexcel-formulacountif
Steve G.
Steve G.

Reputation: 409

COUNTIF used on two different cell ranges

I'm having a little trouble with figuring out an Excel formula. What I have are two columns of data in my business, one that tracks if I have a paper receipt for a transaction and one if I have an e-receipt for that same transaction. To this end, I have two columns:

Transaction                 Receipt     E-receipt
===========                 =======     =========
Amazon.com                     Y            Y
Local copy center              Y            N
Business supply store          N            Y

And so on.

I have two cells that count the number of yeses in each column vs. the total number of transactions, COUNTIF(K3:K6, "Y") &"/"&COUNTA(K3:K6). This would output, in this case, "2/3" for both the receipt and e-receipt columns, i.e. "User, you have two out of the three necessary receipts. Go chase after that missing third one."

What formula must I use to get a count of the number of receipts I have for both, i.e. if there's a Y in both receipt and e-receipt columns for any given transaction? I've tried using AND, IF, COUNTIF, COUNTIFS (which I learned about from here, adding the Y's to see if Y is represented as a 1, converting these Y and N to true and false and adding them that way, etc. But nothing seems to work. Each combination of formulas and ideas has its own roadblock. I'm always getting #VALUE# or an error message.

Anyone have any ideas? I'm hoping to do this without macros or hidden cells.

Upvotes: 0

Views: 173

Answers (1)

vanathaiyan
vanathaiyan

Reputation: 1070

You can use the below formula,for finding the count of Y in both Receipt & E-receipt

 =COUNTIFS(A1:A3,"Y",B1:B3,"Y")

Similarly you can use the same formula for other combinations also.

Upvotes: 2

Related Questions