Excel formula....

[CBurcik]

I know this is probably very basic but is driving me nuts.


I want excel to return a count based on values in two seperate columns.....



orange pear
apple orange
apple pear
peach orange
apple pear


(damn i must be hungry)

I want the formula to return the number 2 for the amount of times apple and pear are in the same row.....


help GREATLY appreciated...

[SeedOfChaos]

I don't know how neat your sheet has to be, but in the case it can be messy, I'd do the following ....

I assume that your two columns are A and B, starting at row 1. Now, write a formula C1:

=IF(AND(IF(A1="apple",1,0),IF(B1="pear",1,0)),1,0)

fill this formula down along the data. This formula will display "1" in the column C when column A reads "apple" AND column B reads "pear"; it will display "0" if there's any other text in either column.

Next, simply make a sum formula for column C, and you'll have the number of times that the A is "apple" and the B is "pear".

In case the order does not matter.... simply use a different formula, one like this...

=IF(OR(AND(IF(A1="apple",1,0),IF(B1="pear",1,0)),AND(IF(A1="pear",1,0),IF(B1="apple",1,0))),1,0)

I think getting all this into one cell could be difficult... if neatness is an issue, consider putting the formulas either in cells outside the print area or with white text color or a zero-width column...

Hope this helps,
Ronald