Need help with Formula when I insert a column

[shanethrapp]

I have an end column that I am trying to sum up from the cells in the earlier columns, and I need the formula to also account for any new columns that I insert into it.

I also have an IF statement in there as well.
So my current Formula is this: =SUM(IF(C2>0,1,0),IF(F2>0,1,0) that creates a total in the Total column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]bad[/TD]
[TD]good[/TD]
[TD]date[/TD]
[TD]bad[/TD]
[TD]good[/TD]
[TD]date[/TD]
[TD]total[/TD]
[TD]Item[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001234[/TD]
[TD]59[/TD]
[TD]1[/TD]
[TD]5/14[/TD]
[TD]28[/TD]
[TD]2[/TD]
[TD]5/14[/TD]
[TD]2[/TD]
[TD]1001234[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My question, how do I keep the equation going if I insert a new column after the 2nd set of columns, consisting of another set of bad/good/date columns, so that it would add up the new column and include the new columns? So that it shows =SUM(IF(C2>0,1,0),IF(F2>0,1,0),IF(I2>0,1,0) or whatever it would look like with the new columns added in???

 

[njimack]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	bad	good	date	bad	good	date	total	Item			COUNT
2	1001234	59	1	May-14	28	2	May-14	2	1001234			2
Sheet1

 

[shanethrapp]

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

Item

bad

good

date

bad

good

date

total

Item

*

*

COUNT

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1001234[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]May-14[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]May-14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1001234[/TD]

[TD="bgcolor: #ffff00, align: right"]2[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula L2 =SUMPRODUCT(--(A$1:I$1="good"),--(A2:I2>0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks for the help but, that did not work. It does not add the columns up to create the correct qty in H2.

In the case of your spreadsheet, I need C1 (if it's greater than 0)to = 1 in H2. but then also add in F2 (if it is greater than 0) to add another 1 to make H2 equal 2. No matter what the number is in the good column, if it is greater than 0, it will add 1 to H2. And I need it to be dynamic, so that if I add in another set of columns (bad/good/date) that it adds in that good column as well.

 

[njimack]

Thanks for the help but, that did not work. It does not add the columns up to create the correct qty in H2.

In the case of your spreadsheet, I need C1 (if it's greater than 0)to = 1 in H2. but then also add in F2 (if it is greater than 0) to add another 1 to make H2 equal 2. No matter what the number is in the good column, if it is greater than 0, it will add 1 to H2. And I need it to be dynamic, so that if I add in another set of columns (bad/good/date) that it adds in that good column as well.

That's exactly what my answer does. The formula in L2 counts how many "good" columns are greater than zero.

 

[shanethrapp]

That's exactly what my answer does. The formula in L2 counts how many "good" columns are greater than zero.

Yes, but I need it so that if I add another set of Good/Bad/Date Column that it adds those in as well. I have attached an image for you to see what happened when I added another set of columns.
When I added the columns N,O,P it did not change the Count Column

https://docs.google.com/document/d/1RcPK9GHliHPQUTRCLcwYw0pLk_qZQiUYiRQDqAZUFVY/edit?usp=sharing

 

[njimack]

I can't see your picture. To ensure additional columns are included, all you need to do is ensure at least one blank column is included within the SUMPRODUCT range, and make sure you insert columns to the left of this. In the example below, make sure you insert columns to the left of column H.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Item	bad	good	date	bad	good	date		total
2	1001234	59	1	May-14	28	2	May-14		2
Sheet1

 

[shanethrapp]

OK, that worked, Thanks!