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)>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)>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)>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...