How to automatically sum cell with some condition

[MushroomJ]

Hi again and Good Afternoon!

I hope someone can help....

I would like to sum all the unhidden income of the household. Unfortunately I do not know how to set a command..

What I want is to automatically sum all unhidden individual income to household income in every household.
In column c Numbers represent as a household number. Number plus letters means it is a different family in that household but their individual income should be included in the household income since they live in a similar house.



I dont know how to translate this to formula,., if column c has a number then add the individual income of rows until the last blank cell following

Sample: if column c7 has a number then add column e7:12 excluding hidden cells then start again if
c13 contains a number then sum e13:e18..

Please see my attached sample.

Thank you in advance

[josephteh]

Change the function number from 9 to 109, formula =SUBTOTAL(109,E7:E12).

[ORoos]

Hi there,

You currently have in cell F7 the formula: =SUBTOTAL(9,E7:E12)
Replace the 9 (sum) with 109 (sum visible): =SUBTOTAL(109,E7:E12)

This will exclude the hidden cells in your range.

[MushroomJ]

Thanks for noticing.,

Would you mind helping me solve the formula in F column?.

[MushroomJ]

Change the function number from 9 to 109, formula =SUBTOTAL(109,E7:E12).

Thank you..

[ORoos]

Hi again,

In cell F7, try:

Please Login or Register  to view this content.

then pull down.

NOTE: for this formula to work, enter all values in column C as 'Text'
Format cell as text and enter number, or as ` in front of number.
The formula then will set the range to just before the next cell containng text.

[MushroomJ]

Hi again,

In cell F7, try:

Please Login or Register  to view this content.

then pull down.

NOTE: for this formula to work, enter all values in column C as 'Text'
Format cell as text and enter number, or as ` in front of number.
The formula then will set the range to just before the next cell containng text.

Hi... Your given formula really worked.

What I observed is on the last household number., I need to put a text or ' in the last part in order for the formula to work for the lat household income.

Since this column will be modified to text I will make this column as a helper.. Because at present, that column is used to count how many are household, in my checker i put a count and count a format to see the difference between household and familiar.

Thank you so much...

[wk9128]

worksheet name : District I , F7 cell formula , drag down

[MushroomJ]

worksheet name : District I , F7 cell formula , drag down

Hi, I tried this formula but unfortunately it did not work. It did not sum the individual income of the household..

[ORoos]

What I observed is on the last household number., I need to put a text or ' in the last part in order for the formula to work for the lat household income.

Correct, there will be an error if no final value is found. You could go around this by wrapping the formula into an IFERROR statement and just make the last range X cells long (20 in the sample formula below).

Please Login or Register  to view this content.

Thanks for the Rep

[MushroomJ]

Correct, there will be an error if no final value is found. You could go around this by wrapping the formula into an IFERROR statement and just make the last range X cells long (20 in the sample formula below).

Please Login or Register  to view this content.

Thanks for the Rep

I tried this option.. This formula has same result however I will not need to put any value in the last household. I would rather use this formula than the first one.

Thanks a lot.....