Silly Question But Stuck

[JALonsdale]

I want to calculate two cells based on the number of items I have within sixteen columns on each row.


[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]
[/TD]
[TD]04
[/TD]
[TD]06
[/TD]
[TD]08
[/TD]
[TD]10
[/TD]
[TD]12
[/TD]
[TD]14
[/TD]
[TD]16
[/TD]
[TD]18
[/TD]
[TD]20
[/TD]
[TD]
[/TD]
[TD]Next
[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Line1[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Should Be 28[/TD]
[/TR]
[TR]
[TD]Line2[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08[/TD]
[TD]Should be 10[/TD]
[/TR]
[TR]
[TD]Line3[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Should be 40[/TD]
[/TR]
</tbody>[/TABLE]

I have figured out the formula in blue as it was rather simple...

=(((COUNTA(b2:b10)*2)+4)

But updating the Total Column is proving more of a challenge.
In this chart there is a line in orange, that cannot be a part of the finished product... Therefore how can I update the total
based on the fact that the first check box value is 4, and it increases each time by 2; without having the 4,6,8,10,12,etc... line in it.

Any feedback would be of much help.

 

[JLGWhiz]

Looks like

=(COUNTA(B2:K2)*2 )+ 4

in M2 and

=SUM(B2:K2)+M2

in N2 based on sample.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][td="bgcolor:#C0C0C0"]

M​

[/td][td="bgcolor:#C0C0C0"]

N​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td]

04​

[/td][td]

06​

[/td][td]

08​

[/td][td]

10​

[/td][td]

12​

[/td][td]

14​

[/td][td]

16​

[/td][td]

18​

[/td][td]

20​

[/td][td][/td][td]Next[/td][td]Total[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Line1[/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td="bgcolor:#E5E5E5"]

28​

[/td][td]M2: =SUMPRODUCT((B2:J2 = "x") * $B$1:$J$1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Line2[/td][td]

x​

[/td][td]

x​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td="bgcolor:#E5E5E5"]

10​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Line3[/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td="bgcolor:#E5E5E5"]

40​

[/td][td][/td][/tr]
[/table]

 

[JALonsdale]

Looks like

=(COUNTA(B2:K2)*2 )+ 4

in M2 and

=SUM(B2:K2)+M2

in N2 based on sample.

Here's what's going on. The first box adds a value of 4, then the next value adds 6, then 8, then 10 and so fourth.

Like I said however, the orange line cannot be in the final product. I'm not sure if I should do a if case tier or sumproduct something.

[TABLE="class: cms_table_grid, width: 100, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]04
[/TD]
[TD]06
[/TD]
[TD]08
[/TD]
[TD]10
[/TD]
[TD]12
[/TD]
[TD]14
[/TD]
[TD]16
[/TD]
[TD]18
[/TD]
[TD]20
[/TD]
[TD]
[/TD]
[TD]Next
[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Line1[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Should Be 28[/TD]
[/TR]
[TR]
[TD]Line2[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08[/TD]
[TD]Should be 10[/TD]
[/TR]
[TR]
[TD]Line3[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Should be 40[/TD]
[/TR]
</tbody>[/TABLE]

04 + 06 + 08 + 10 is 28, that's how I got that number on line 1.
04 + 06 should be on line 2, 10.
and then the final line is 40, as 4 + 6 + 8 + 10 +12 = 40.

 

[JALonsdale]

thanks.

 

[JALonsdale]

it works... Thank you.