I have two columns - Column A has 13 amounts in 13 cells. Column B has 5 amounts in 5 cells. The total of Column A and Column B is equal, however, the amount in one cell of Column B is equal to a sum of two or three or four cells (I am not sure - because that is what I have to find) in column A. I want to identify the number of cells in Column A whose total is equal to the amount in one cell of column B.

In the below example: the amount in first cell of column B which is 22 is equal to a sum of first five cells in column A (2+3+5+4+8) and the second amount in Column B, which is 91 is equal to sum of seventh and eight cell (85+6).

Excel Example

Please help me how do I identify/highlight the cells in column A whose total is equal to one cell of column B.

Regards

1 Answer
1

This will identify the cells, but does nothing to its formatting (no cell highlithing)

Put :

E2 = 1
E3 = 2
E4 = 3
E5 = 4
E6 = 5
E7 = 6
E8 = 7
E9 = 8
E10 = 9
E11 = 10
E12 = 11
E13 = 12
E14 = 13
H1 = "A row#"
I1 = "B row#"


and formulas..

F2 =IF(G1="",A2+F1,A2)
G2 =IF(IFERROR(MATCH(F2,B:B,0),0)>0,INDEX(B:B,MATCH(F2,B:B,0)),"")
H2 =IF(G2="","",E2)
I2 =IF(G2="","",MATCH(F2,B:B,0)-1)


select F2:I2 and drag until I14. I think that's it.. (:

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.