0

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.

Community
  • 1
  • 1
Steve G.
  • 411
  • 1
  • 7
  • 21
  • Assuming your Receipt is in column `K` and your E-receipt is in column `L`, then why will `=COUNTIFS(K3:K6, "Y", L3:L6, "Y")` not work for you? Please show what you have tried and what not worked. – Axel Richter Jul 20 '16 at 12:10

1 Answers1

2

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.

vanathaiyan
  • 935
  • 1
  • 10
  • 19
  • Doh! Thanks, @vanathaiyan. I have no idea what I was doing wrong, but I must have been getting too complicated. I just plugged your line it and, for some reason, it worked like a charm. Feel like an idiot now, but happy it works. Thanks a ton! – Steve G. Jul 21 '16 at 16:10