# Boards

## Excel-lent question!

One column contains text data, as does the column next to it. I need a formula that tells me how many times column A=x and column B=y. I have little to no experience of IF, AND and OR.

Pivot table shmivot table, I need a formula.

Cheers

## Pass!

## You could create a new column next the A called which contains a 1 if A=x and a 0 otherwise

Them at the bottom of this column get the Sum on the column.

I don't use Excel but it would be pretty easy to do.

## It's good,

but I'd need a column for every different entry in the first column.

There must be a way, shirley?

## countif(a1:a50,"x")

?

## ^this

## Yeah,

that sorts the first column out. What about when I want to know how many times x is in column A and y is in column B?

## brackets!

=(COUNTIF(A1:A50,"x")+COUNTIF(B1:B50,"y"))

## actually

that's nonsense

## ^yeah, that

## i don't think we're understanding the problem

(i think) J_B wants to know how many times A = x AND B = y in the same row so that countif willnae work.

i've never used Excel so i dinnae know.

## what exactly do you want it to do?

put the total of the number of x's and y's in the same cell? that would be possible if that's what you need

## I don't think I've worded this very well looking back at it.

Where column A=x AND column B=y.

Say column A is names and column B is Yes/No. One formula in one cell that tells me how often column A=Adam and column B=Yes.

I can total column A on it's own, I can total column B on it's own. What about the combined results of two columns, without a pivot table?

## VBA?

## Yeah, could do.

Might have to, I guess

## only way i can think of

is to have a column with this all the way down

=AND(A1="x",B1="y")

=AND(A2="x",B2="y")

etc

this will return "true" if a=x and b=y and false otherwise. Then you can use countif to add up the trues: =COUNTIF(C1:C50,TRUE)

Then you can hide the column with the trues and falses in.

## There probably is a better way of doing it than that

but I can't think of it right now

## I

think between you and blaaaaaaaast I've got it now, it's all IF and AND.

Cheers, boom shanka to you both xx

## as i said down there VVVVV

=SUMPRODUCT(--(A1:A50="X"),--(B1:B50="Y"))

## yeah, countif(a:a, "x")

## no idea

how's it going on the minijudgebII front?

## We are now officially overdue.

We have a date to go in if nothing happens before that, but right now we're playing the waiting game...

## I don't have excel in front of me but

how about sticking in a column next to A with a formula returning a 1 if the cell has what is needed in it and then do the same with the next column C. Then a 5th column with a sum of B & D in it - if that column equals 2 then your requirement has been met and then do a count on all 2s.

I think that could work if my words make any sense.

## I understand what you're saying,

but there is one column with 3 possible results and then three columns with 2 possible results. I think to do what you say when I want the results of columns 1 and 2, 1 and 3 and 1 and 4 is going to take a lot of arsing about.

I was hoping that there might be a formula along the lines of =countif(columnA(Something)) AND (columnB(SomethingElse))

which I could then repeat for the other columns, if you see what I mean

## Or do it in one column:

=IF(AND(A1="X", B1="Y"), 1, 0)

then do =SUM(C1:C10) under it...

## I thought AND might have something to do with it

I'd need to know every possible combination for this and give it a number if that was what I wanted to know, wouldn't I? And then use countif in a different table...

## TAADAA!!!!

=SUMPRODUCT(--(A1:A50="X"),--(B1:B50="Y"))

## what do I win?

## Does that work?

I've gone with IF(AND(A1="This",B1="That"),1,IF(AND(A1="Something",B1="SomethingElse),2,etc.

and then SUMIF 1,2,3 etc. in another table.

I'm going to try this as well though, just cos I like the look of it

## sure does

## oh wow, well done

how did you find that one?

## Google