General Betting

Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
13 Dec 13 12:07
Date Joined: 21 Dec 10
| Topic/replies: 961 | Blogger: U.A.'s blog
Hello I wonder if anyone would be kind enough to help with an Excel formula problem.

I have two columns over 2000 rows long and I want a formula that looks at column A and column B and says that if cell A1=B1 then count it. Same with cell A2=B2 and so on and then at the bottom it gives a total of all the cells where the A cell is the same as the corresponding B cell.

In the past i would just do a column C and say if cellA1=CellB1 then 1 otherwise 0, copy it all the way down and add it up at the bottom, but there are too many columns that I want to compare so I want to do it all in one hit. It's may well be something like the countif function but I can't seem to get anything to work.

Any help would be gratefully appreciated.
Pause Switch to Standard View Excel question
Show More
Report JLivermore December 13, 2013 2:11 PM GMT
this seems difficult
super easy if you write a VBA function, but I wouldn't go there unless you were confident with macros.
Report Ghetto Joe December 13, 2013 2:37 PM GMT
Probably easier with VBA but as long as you know how to enter the data as an array you could use a simple formula like this to count any' duplicates in the range A1 to A3000, obviously change6 the cell references to what you need, the countblank bit is there as any empty cells in the range would be treated as a match so we take them off the sum

=SUM(IF(A1:A3000 = B1:B3000, 1, 0))-COUNTBLANK(A1:A3000)

Just remember excel has to treat the data as an array you you can't just type in the formula and hit ENTER you have to get excel to treat it as an array by pressing
CTRL-SHIFT together then ENTER.
Report JLivermore December 13, 2013 2:46 PM GMT
I was attempting SUM(A1:A3000 = B1:B3000) in an array formula, but couldn't get there

nice solution from Ghetto Joe
Report U.A. December 13, 2013 8:51 PM GMT
Hi guys, thanks for the responses. I don't know much about VBA so will have to investigate this but at least i have a bit of a starting point now.

Report U.A. December 14, 2013 8:35 AM GMT
Ahh genius, finally figured out what you were saying, didn't really know anything about ARRAY. I'd seen it mentioned in the boxes when you click on the function button for various formulas but I just assumed it was a jazzy term for a load of cells. Anyway got it working now so thanks for that. I think VBA is beyond me so great to have a non VBA solution.

Post Your Reply
<CTRL+Enter> to submit
Please login to post a reply.


Instance ID: 13539