Dynamic range of column in sum range with criteria

[jeetusaini85]

Hi Guys,

I hope all is well at your end.

I need your help to create a formula that I am trying to create for 2 days and now I give up.

Below is the table in which column A has multiple same values and in B column the value will always be 0 in the first cell corresponding to column A and in 2nd cell of the B column will be a random value.

So, in column B3 I want to add 10 including B2 value and so on till the column A value change. Like, B3 would be 5+10=15, B4=5+15+10=30....

Hope, It makes sense, Please assist.

A	B
1	0
1	5
1	(B1+B2)+10=15
1	(B1+B2+B3)+10=30
1	(B1+B2+B3+B4)+10=50
2	0
2	10
2	(B6+B7)+10=20
2	(B6+B7+B8)+10=40
2	(B6+B7+B8+B9)+10=80
3	0
3	6
3	so on
3
3

 

[Eric W]

Regrettably, my work doesn't allow me to download files from the internet so I can't see your file with the expected results. It's possible I could tweak your formula a bit, but if it works and gives you the results you want, then I think you're set. Good luck!

 

[jeetusaini85]

Thanks Eric,

Appreciated your help!

Just want to know that how can i match two columns in single match function like:

=ROUND(($L4+SUM(AC4:INDEX(AC:AC,MATCH(B:B,B:B,0)))+IF(B4<>B3,0,IF(COUNTIF(B$1:B3,B4)<>1,SUMIF(B$1:B3,B4,AD$1:AD3))))*$P4/360*W4,0)

I want to match column J:J as well

 

[Eric W]

I've never been a fan of the MATCH(B:B,B:B,0) formulation. I prefer MATCH(B4,B:B,0), and even better is MATCH(B4,B1:B100,0). The MATCH(B:B,B:B,0) requires something called implicit intersection, which is sort of going away with the newest versions of Excel. So given that, you can try changing the MATCH to:

MATCH(B4&"|"&J4,$B$1:$B$1000&"|"&$J$1:$J$1000,0)

which would find the first match where B4 and J4 both match. If you don't have the latest version of Excel 365, you'll need to confirm your formula with Control+Shift+Enter. The 1000 in the formula is the maximum row of your table, or something not too far after.

 

[jeetusaini85]

Thanks Eric for your support and guidance.