Excel help

Zenduri · 2 Oct 2013 at 13:11

Quick question as im having a brain melt session at work

Simplified version:

I have 2 columns with data in each. lets say both contain a simple YES or NO comment.

I only want to count the number of cells where for example both column A and B contain the variable of Yes. on the same line

Im familiar with for example =countif(A1:A85, "yes") to return the number of cells with 1 argument but want it to only count if its assessing both columns and only counting where both are Yes

I assumed and failed hard that it would be something like: =countif(A1:A85, "yes")And(B1:B85, "yes")

Can anyone help my excel noobishness

Joshct · 2 Oct 2013 at 13:16

If you need to do it one time: Custom sort where you sort by first column and then by second column, then just count the rows.

If you don't want to move data or need a repeatable value i can't help but i'm bored and trying to figure it out

HumanShield · 2 Oct 2013 at 13:16

=COUNTIFS(A1:A85,"yes",B1:B85,"yes")

assuming you wanted a third column with a 1 or a 0 depending on the content of columns A and B

WillyNelson · 2 Oct 2013 at 13:18

=SUMPRODUCT((range1="yes")*(range2="yes"))

Haha! Yea, or COUNTIFS. I learned my trade mostly prior to coutifs and sumifs.

rhysduck · 2 Oct 2013 at 13:19

A---B----C-------D
1--yes--no--- =countif(a1:b1, "Yes") (returns 1)
2--yes--yes-- =countif(a2:b2, "Yes") (returns 2)
3--no---no---- =countif(a3:b3, "Yes") (returns 0)

Edit: The above works fine for me.

DanielMMS · 2 Oct 2013 at 13:19

=countifs(A1:A85,"yes",B1:B85,"yes")

Zenduri · 2 Oct 2013 at 13:24

HumanShield said:
=COUNTIFS(A1:A85,"yes",B1:B85,"yes")

assuming you wanted a third column with a 1 or a 0 depending on the content of columns A and B

Legend! Works thanks, this is what I was after.

Zenduri · 2 Oct 2013 at 13:24

Can An Admin close this please