Counting the sum of a cell based on an adjacent cells value

Hi all!

I'm a quantity surveyor using excel on a daily basis trying to figure out another formula to make my workflow more efficient.

My current problem is the sheet in which i measure drainage.

Drainage Excel.png

I have conditional formatting on column E, which puts the cell at a specific colour based on the 'average depth' of the drainage. This way i can quickly see which lengths are at which depths.

AC to AE is my collection. All lengths are added up which are at the relevant depths, but i have to manually select each length in column J. Sometimes due to the colours being similar or me trying to be too quick, it's easy to miss a cell or select one too many, which then takes a while to mitigate so everything tallies up again.

MY QUESTION!

Is there a way to use COUNTIF in a way that counts the value in column J IF the value in column E is between 2 figures.

e.g. if column E is between 0.1-0.5, include the length in column J. Total in AE6 etc.

Thanks!

Thanks for your response!

I think i have attached a small representation of what i'm looking at.

I need a formula for 'Total(m)' to add the 'Length(m)' based on the 'AV Depth' into the corresponding depths.

I've included in my sheet what i do currently, but imagine there are hundreds of lines, this can be quite tedious!

I'm not too sure how to use the CountIf. If you could give me a hand i'd really appreciate it!

This has worked a treat! that's amazing. I really appreciate it!