hello alll,,
i have a question about what formula or function is to be entered in the following case::
Suppose
Col A: Col B
X Y
W X
Y Z
X X
X Z
Z Y
W Y
X Y
W Z
X X
I want a function which can determine(count) the number of same entries in
both Columns...
As in above(shown in Italics) if X in column A matches adjacent X in column B then count value should be 2.
Pliz help....
Thanks in advance.........
G'day
If using excel 2007
=COUNTIFS(A1:A10,"x",B1:B10,"x")
Cheers
Last edited by ratcat; 04-25-2008 at 04:04 AM.
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
thanks for r help but i am using Excel 2003....can u pliz tell me what function to use in xcel 2003???
G'day Undertaker17,
Sorry for the slow response, Excel 2007 has may me forgotten some of 2003 formulas. I had to do some research cos I only got half the formula right.
*Drum roll*
Hint: You can also use cell references eg C1 = X=SUMPRODUCT(--(A1:A10="X"),--(B1:B10="X"))
=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C1))
HTH
Cheers
Last edited by ratcat; 04-25-2008 at 08:24 AM.
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
A slight variation on the foregoing is:
This will count the number of times a match exists in the adjacent column for any letter.=SUMPRODUCT(--(A1:A10=B1:B10))
Another variation in case you wish to expand your range and dont want to also count blanks as equal.
=SUMPRODUCT(--($A$1:$A$5000=$B$1:$B$5000))-COUNTBLANK($A$1:$A$5000)
- Portuga
There is no such thing as a problem, only a temporary lack of a solution
In formulas,you might need to replace ; with , depending on your XL version
thank u all..
i have found the solution to which function to use...thank u once again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks