# sum up n cells from 1 to n and color them if total isn't matching

1. ## sum up n cells from 1 to n and color them if total isn't matching

Hello,

I'm trying to solve the following without VBA:

Unbenannt.JPG

The left side is the total of the right side.
Now I'm trying to highlight the right cell group if the summation is not equal to the total on the left side.
Actually that's not hard to achieve, but I want that it automatically detects his left side parent. If that would be kind of possible then I could set the formula for the whole column.

My idea is:
- get the parent cell (at D3 it would be B3, at D4 it should be B3)
- subtract the cell value from the parent cell and put it at the parent row at e.g. F3
- in the first case it should does the steps above 3 times; then if F3 is not zero D3, D4, D5 should highlight in red.

BUT I don't know if it's even possible, because I couldn't find even the first step.  Register To Reply

2. ## Re: sum up n cells from 1 to n and color them if total isn't matching

Hi,

in the Excel file attached I'm using two helper columns to activate conditional formats

In E3

=IF(A3,IFERROR(SUM(C3:INDEX(C4:C\$100,MATCH(1,INDEX(--(B4:B\$100<>""),),0)-1)),SUM(C3:C\$100))<>A3,"")

in F3

=IF(A3,IFERROR(SUM(D3:INDEX(D4:D\$100,MATCH(1,INDEX(--(B4:B\$100<>""),),0)-1)),SUM(D3:D\$100))<>A3,"")

Conditional formatting rules:

in C3, rule to be extedended below:

=LOOKUP(2,(1/(E\$3:E3<>"")),E\$3:E3)

In D3

=LOOKUP(2,(1/(F\$3:F3<>"")),F\$3:F3)

Regards  Register To Reply

3. ## Re: sum up n cells from 1 to n and color them if total isn't matching

Hi,

everything worked fine until I have just on item for the second part.

Unbenannt.JPG

With the last two columns I tried to display the difference, if there is any. But is there just on item it takes the number from the next main row. Is there a nice way to solve this too?  Register To Reply

4. ## Re: sum up n cells from 1 to n and color them if total isn't matching

See attached:

Helper in F

=IF(A3<>"",MAX(\$F\$2:F2)+1,F2)

in E

=IF(A3<>"",SUMIF(F:F,F3,C:C)=A3,"")

CF based on test if E is FALSE

=\$E3=FALSE  Register To Reply