# countif 2 cells are equal, when 1 cell is equations that could be letters or numbers

1. ## countif 2 cells are equal, when 1 cell is equations that could be letters or numbers

I thought this would be easy enough to do but after a couple of days messing around on and off with formula's i am no closer to getting the result i want:

I have 2 rows of cells being compared using the formula:
=IF(E5>F5,"1",IF(E5<F5,"2", IF(E5=F5,"X")))

This works fine but then my next formula is suppose to count if this matches another range of cells (e.g. cells I-L 5:16), i tried:
=SUMPRODUCT((K5:K16=M5:M16)*1)
But this only works for the x's unless i use the abs() function which throws an error any time I have x's.

I am at a bit of a loss now so any assistance would be kindly received

2. ## Re: countif 2 cells are equal, when 1 cell is equations that could be letters or numb

not sure if it fixes the exact issue, but the 1 and 2 in the formula should probably not be within quotes, if you want actual numbers.

e.g.

=IF(E5>F5,1,IF(E5<F5,2, IF(E5=F5,"X")))

3. ## Re: countif 2 cells are equal, when 1 cell is equations that could be letters or numb

You are some kind of magician!

That sort everything just nicely. Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1