Lets say you have a spreadsheet that 2500 rows with 10 or so columns...i need
to be able to calculate subtotals based on blanks and non-blanks of several
columns...ie: if column C is blank, and if column E is non-blank, and column
H is non-blank, then give me the totals of column J...I know you can use a
sort to do this, but its not practical...i tried a sum(if( formula, but no
luck....I appreciate your help...

IF the cells have numbers, then simply do a sum of the cells to determine if
they arre blank. the SUM function returns zeroes for blank cells.

"Rover" wrote:

Lets say you have a spreadsheet that 2500 rows with 10 or so columns...i need
to be able to calculate subtotals based on blanks and non-blanks of several
columns...ie: if column C is blank, and if column E is non-blank, and column
H is non-blank, then give me the totals of column J...I know you can use a
sort to do this, but its not practical...i tried a sum(if( formula, but no
luck....I appreciate your help...

Thanks Joel...That is exactly what I am trying to do, but I cant seem to
figure out what the foormula should be.

Lets say you want the sums in column T, if column B is blank, column C is
blank and column D is non-blank.
or

You want the sum in column T, if column B non blank, column is non blank and
column D is blank...

I been trying to figure this out for two days now...any help is appreciated...


"Joel" wrote:

IF the cells have numbers, then simply do a sum of the cells to determine if
they arre blank. the SUM function returns zeroes for blank cells.

"Rover" wrote:

Lets say you have a spreadsheet that 2500 rows with 10 or so columns...i need
to be able to calculate subtotals based on blanks and non-blanks of several
columns...ie: if column C is blank, and if column E is non-blank, and column
H is non-blank, then give me the totals of column J...I know you can use a
sort to do this, but its not practical...i tried a sum(if( formula, but no
luck....I appreciate your help...

=IF(AND(SUM(T:T)=0,SUM(C:C)=0,SUM(D:D)0),SUM("T:T "),"")

"Rover" wrote:

Thanks Joel...That is exactly what I am trying to do, but I cant seem to
figure out what the foormula should be.

Lets say you want the sums in column T, if column B is blank, column C is
blank and column D is non-blank.
or

You want the sum in column T, if column B non blank, column is non blank and
column D is blank...

I been trying to figure this out for two days now...any help is appreciated...


"Joel" wrote:

IF the cells have numbers, then simply do a sum of the cells to determine if
they arre blank. the SUM function returns zeroes for blank cells.

"Rover" wrote:

Lets say you have a spreadsheet that 2500 rows with 10 or so columns...i need
to be able to calculate subtotals based on blanks and non-blanks of several
columns...ie: if column C is blank, and if column E is non-blank, and column
H is non-blank, then give me the totals of column J...I know you can use a
sort to do this, but its not practical...i tried a sum(if( formula, but no
luck....I appreciate your help...

Thank you Joel...Does that also work if the cell are date formated? So when
I say blank for no blank, I am referring to dates. What do I use for dates?
I am assuming ="" for blanks and <"" for non blanks...

"Joel" wrote:

=IF(AND(SUM(T:T)=0,SUM(C:C)=0,SUM(D:D)0),SUM("T:T "),"")

"Rover" wrote:

Thanks Joel...That is exactly what I am trying to do, but I cant seem to
figure out what the foormula should be.

Lets say you want the sums in column T, if column B is blank, column C is
blank and column D is non-blank.
or

You want the sum in column T, if column B non blank, column is non blank and
column D is blank...

I been trying to figure this out for two days now...any help is appreciated...


"Joel" wrote:

IF the cells have numbers, then simply do a sum of the cells to determine if
they arre blank. the SUM function returns zeroes for blank cells.

"Rover" wrote:

Lets say you have a spreadsheet that 2500 rows with 10 or so columns...i need
to be able to calculate subtotals based on blanks and non-blanks of several
columns...ie: if column C is blank, and if column E is non-blank, and column
H is non-blank, then give me the totals of column J...I know you can use a
sort to do this, but its not practical...i tried a sum(if( formula, but no
luck....I appreciate your help...

Dates are really numbers formated as a date. It you put a date into a cell
and then change the cell to number forat you will see a number.


The number for the date follows the following rules
1) Jan 1, 1900 is one
2) Each day is also one. the days increment from Jan 1
3) Each hour is 1/24
4) Each minute is 1/1440 which is 24 * 60.
5) All other time is fraction of the day with 1 day equaling one.

"Rover" wrote:

Thank you Joel...Does that also work if the cell are date formated? So when
I say blank for no blank, I am referring to dates. What do I use for dates?
I am assuming ="" for blanks and <"" for non blanks...

"Joel" wrote:

=IF(AND(SUM(T:T)=0,SUM(C:C)=0,SUM(D:D)0),SUM("T:T "),"")

"Rover" wrote:

Thanks Joel...That is exactly what I am trying to do, but I cant seem to
figure out what the foormula should be.

Lets say you want the sums in column T, if column B is blank, column C is
blank and column D is non-blank.
or

You want the sum in column T, if column B non blank, column is non blank and
column D is blank...

I been trying to figure this out for two days now...any help is appreciated...


"Joel" wrote:

IF the cells have numbers, then simply do a sum of the cells to determine if
they arre blank. the SUM function returns zeroes for blank cells.

"Rover" wrote:

Lets say you have a spreadsheet that 2500 rows with 10 or so columns...i need
to be able to calculate subtotals based on blanks and non-blanks of several
columns...ie: if column C is blank, and if column E is non-blank, and column
H is non-blank, then give me the totals of column J...I know you can use a
sort to do this, but its not practical...i tried a sum(if( formula, but no
luck....I appreciate your help...