Forums

General Betting

There is currently 1 person viewing this thread.
U.A.
13 Dec 13 12:07
Joined:
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.

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
sort by:
Show
per page
Replies: 5
By:
JLivermore
When: 13 Dec 13 14:11
this seems difficult
super easy if you write a VBA function, but I wouldn't go there unless you were confident with macros.
By:
Ghetto Joe
When: 13 Dec 13 14:37
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.
By:
JLivermore
When: 13 Dec 13 14:46
I was attempting SUM(A1:A3000 = B1:B3000) in an array formula, but couldn't get there

nice solution from Ghetto Joe
By:
U.A.
When: 13 Dec 13 20:51
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.

Cheers
By:
U.A.
When: 14 Dec 13 08:35
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.

Thanks
sort by:
Show
per page

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
‹ back to topics
www.betfair.com