General Betting
There is currently 1 person viewing this thread.
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.
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.
sort by:
Show
per page
Replies: 5
|
By:
this seems difficult
super easy if you write a VBA function, but I wouldn't go there unless you were confident with macros. |
|
By:
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:
I was attempting SUM(A1:A3000 = B1:B3000) in an array formula, but couldn't get there
nice solution from Ghetto Joe |
|
By:
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:
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 |