Sumproduct ... Empty Cells vs Spaces?
 

Excel 2000 ... I have data I copy/paste to Excel from an
Access query (I know nothing about Access so I munipulate
data in Excel) ... My data consist of 40 columns by 10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns (you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells with "spaces" ...
(at least I assume "spaces" because they appear empty, but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to accommodate
conditions for those 3 Columns that contain data, &/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha

Hi
you may post your current formula. As an initial idea: use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im Newsbeitrag
...
Excel 2000 ... I have data I copy/paste to Excel from an
Access query (I know nothing about Access so I munipulate
data in Excel) ... My data consist of 40 columns by 10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns (you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells with "spaces" ...
(at least I assume "spaces" because they appear empty, but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to accommodate
conditions for those 3 Columns that contain data, &/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha

Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) & 2 Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2 other
conditions (for Cols H & L) where Col contains data, empty
cells (or spaces) & I wish empty cells or spaces to be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha






-----Original Message-----
Hi
you may post your current formula. As an initial idea:
use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
Excel 2000 ... I have data I copy/paste to Excel from an
Access query (I know nothing about Access so I
munipulate
data in Excel) ... My data consist of 40 columns by
10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns (you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells with "spaces" ...
(at least I assume "spaces" because they appear empty,
but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to
accommodate
conditions for those 3 Columns that contain data, &/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha



.

Hi
do you mean:
=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)*(TRIM($H$2:$H$ 12000)=""))*(TRIM($L$2:$L$12000)="")))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im Newsbeitrag
...
Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) & 2 Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2 other
conditions (for Cols H & L) where Col contains data, empty
cells (or spaces) & I wish empty cells or spaces to be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha






-----Original Message-----
Hi
you may post your current formula. As an initial idea:
use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
Excel 2000 ... I have data I copy/paste to Excel from an
Access query (I know nothing about Access so I
munipulate
data in Excel) ... My data consist of 40 columns by
10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns (you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells with "spaces" ...
(at least I assume "spaces" because they appear empty,
but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to
accommodate
conditions for those 3 Columns that contain data, &/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha



.

Frank ... OK Shoot me, but my knowledge of Excel (or lack
of it) is limited to hacking only ... & though I use the
double "" often I often have issue with understanding how
Excel is interpreting the Cell containing the double
quote "" (empty cell, space or other?)...

Now ... if a double quote "" equates to an empty cell then
when a single cell is being evaluated by the piece of your
formula extracted below:

*(TRIM($H$2:$H$12000)=""))

Does this equate to ... TRIM cell (remove spaces) ... once
done ... if remainder is an EMPTY cell (the double
quote "") ... TRUE? ... Otherwise FALSE because there
would be visible data remaining in the cell???

Am I anywhere close???

Thanks for sticking with me as I already see this as a
valuable function ... Kha



-----Original Message-----
Hi
do you mean:
=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$B G2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)*(TRIM($H$2:$H $12000)
=""))*(TRIM($L$2:$L$12000)="")))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) & 2
Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2
other
conditions (for Cols H & L) where Col contains data,
empty
cells (or spaces) & I wish empty cells or spaces to be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha






-----Original Message-----
Hi
you may post your current formula. As an initial idea:
use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
.. .
Excel 2000 ... I have data I copy/paste to Excel from
an
Access query (I know nothing about Access so I
munipulate
data in Excel) ... My data consist of 40 columns by
10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns
(you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells
with "spaces" ...
(at least I assume "spaces" because they appear empty,
but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to
accommodate
conditions for those 3 Columns that contain data, &/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha



.



.

Hi
""
would return for all cells that are either
- completely empty
- or contain only a zero length string

The secopnd one would be true if for example the cell contains a formula
which returns "". e.g. the false part of the following IF statement
=IF(A1=1,A1,"")

Also if the TRIM function returns an empty string the formula part would
evaluate to TRUE

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im Newsbeitrag
...
Frank ... OK Shoot me, but my knowledge of Excel (or lack
of it) is limited to hacking only ... & though I use the
double "" often I often have issue with understanding how
Excel is interpreting the Cell containing the double
quote "" (empty cell, space or other?)...

Now ... if a double quote "" equates to an empty cell then
when a single cell is being evaluated by the piece of your
formula extracted below:

*(TRIM($H$2:$H$12000)=""))

Does this equate to ... TRIM cell (remove spaces) ... once
done ... if remainder is an EMPTY cell (the double
quote "") ... TRUE? ... Otherwise FALSE because there
would be visible data remaining in the cell???

Am I anywhere close???

Thanks for sticking with me as I already see this as a
valuable function ... Kha



-----Original Message-----
Hi
do you mean:
=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$ BG2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)*(TRIM($H$2:$ H$12000)
=""))*(TRIM($L$2:$L$12000)="")))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) & 2
Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2
other
conditions (for Cols H & L) where Col contains data,
empty
cells (or spaces) & I wish empty cells or spaces to be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha






-----Original Message-----
Hi
you may post your current formula. As an initial idea:
use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
. ..
Excel 2000 ... I have data I copy/paste to Excel from
an
Access query (I know nothing about Access so I
munipulate
data in Excel) ... My data consist of 40 columns by
10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns
(you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells
with "spaces" ...
(at least I assume "spaces" because they appear empty,
but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to
accommodate
conditions for those 3 Columns that contain data, &/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha



.



.

Frank ...

Zero length string ... What is this? Based on your last
post I am assuming the cell is not empty, but contents not
visible either ... What would be an example of this ... &
would the "space" be an example???

Thanks for the Education ... Kha


-----Original Message-----
Hi
""
would return for all cells that are either
- completely empty
- or contain only a zero length string

The secopnd one would be true if for example the cell
contains a formula
which returns "". e.g. the false part of the following IF
statement
=IF(A1=1,A1,"")

Also if the TRIM function returns an empty string the
formula part would
evaluate to TRUE

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
Frank ... OK Shoot me, but my knowledge of Excel (or
lack
of it) is limited to hacking only ... & though I use the
double "" often I often have issue with understanding
how
Excel is interpreting the Cell containing the double
quote "" (empty cell, space or other?)...

Now ... if a double quote "" equates to an empty cell
then
when a single cell is being evaluated by the piece of
your
formula extracted below:

*(TRIM($H$2:$H$12000)=""))

Does this equate to ... TRIM cell (remove spaces) ...
once
done ... if remainder is an EMPTY cell (the double
quote "") ... TRUE? ... Otherwise FALSE because there
would be visible data remaining in the cell???

Am I anywhere close???

Thanks for sticking with me as I already see this as a
valuable function ... Kha



-----Original Message-----
Hi
do you mean:
=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000= $BG2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)*(TRIM($H$2: $H$12000)
=""))*(TRIM($L$2:$L$12000)="")))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
.. .
Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) & 2
Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2
other
conditions (for Cols H & L) where Col contains data,
empty
cells (or spaces) & I wish empty cells or spaces to be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha






-----Original Message-----
Hi
you may post your current formula. As an initial idea:
use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
.. .
Excel 2000 ... I have data I copy/paste to Excel
from
an
Access query (I know nothing about Access so I
munipulate
data in Excel) ... My data consist of 40 columns by
10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns
(you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells
with "spaces" ...
(at least I assume "spaces" because they appear
empty,
but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE
when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to
accommodate
conditions for those 3 Columns that contain data,
&/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha



.



.



.

Hi
no space would not be an example. Enter
=""
in one cell and you have this kind of 'zero length string'. The cells is not
empty. e.g. =ISBLANK(cell_reference) would return FALSE
But
=cell_reference=""
will return TRUE

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im Newsbeitrag
...
Frank ...

Zero length string ... What is this? Based on your last
post I am assuming the cell is not empty, but contents not
visible either ... What would be an example of this ... &
would the "space" be an example???

Thanks for the Education ... Kha


-----Original Message-----
Hi
""
would return for all cells that are either
- completely empty
- or contain only a zero length string

The secopnd one would be true if for example the cell
contains a formula
which returns "". e.g. the false part of the following IF
statement
=IF(A1=1,A1,"")

Also if the TRIM function returns an empty string the
formula part would
evaluate to TRUE

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
Frank ... OK Shoot me, but my knowledge of Excel (or
lack
of it) is limited to hacking only ... & though I use the
double "" often I often have issue with understanding
how
Excel is interpreting the Cell containing the double
quote "" (empty cell, space or other?)...

Now ... if a double quote "" equates to an empty cell
then
when a single cell is being evaluated by the piece of
your
formula extracted below:

*(TRIM($H$2:$H$12000)=""))

Does this equate to ... TRIM cell (remove spaces) ...
once
done ... if remainder is an EMPTY cell (the double
quote "") ... TRUE? ... Otherwise FALSE because there
would be visible data remaining in the cell???

Am I anywhere close???

Thanks for sticking with me as I already see this as a
valuable function ... Kha



-----Original Message-----
Hi
do you mean:
=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000 =$BG2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)*(TRIM($H$2 :$H$12000)
=""))*(TRIM($L$2:$L$12000)="")))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
. ..
Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) & 2
Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2
other
conditions (for Cols H & L) where Col contains data,
empty
cells (or spaces) & I wish empty cells or spaces to be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha






-----Original Message-----
Hi
you may post your current formula. As an initial idea:
use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
. ..
Excel 2000 ... I have data I copy/paste to Excel
from
an
Access query (I know nothing about Access so I
munipulate
data in Excel) ... My data consist of 40 columns by
10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns
(you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells
with "spaces" ...
(at least I assume "spaces" because they appear
empty,
but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE
when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to
accommodate
conditions for those 3 Columns that contain data,
&/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha



.



.



.

Frank ...

I know this has been a relatively long thread, but I have
gotten an education from this today & hopefully, a few
others have as well. SUMPRODUCT is a great function.

Above said ... My Thanks ... This is one Excel user that
sincerely appreciates the knowledge & support that you,
other MVPs, & the general Excel population of Users bring
to these boards ... Kha


-----Original Message-----
Hi
no space would not be an example. Enter
=""
in one cell and you have this kind of 'zero length
string'. The cells is not
empty. e.g. =ISBLANK(cell_reference) would return FALSE
But
=cell_reference=""
will return TRUE

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
Frank ...

Zero length string ... What is this? Based on your last
post I am assuming the cell is not empty, but contents
not
visible either ... What would be an example of this ...
&
would the "space" be an example???

Thanks for the Education ... Kha


-----Original Message-----
Hi
""
would return for all cells that are either
- completely empty
- or contain only a zero length string

The secopnd one would be true if for example the cell
contains a formula
which returns "". e.g. the false part of the following
IF
statement
=IF(A1=1,A1,"")

Also if the TRIM function returns an empty string the
formula part would
evaluate to TRUE

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
.. .
Frank ... OK Shoot me, but my knowledge of Excel (or
lack
of it) is limited to hacking only ... & though I use
the
double "" often I often have issue with understanding
how
Excel is interpreting the Cell containing the double
quote "" (empty cell, space or other?)...

Now ... if a double quote "" equates to an empty cell
then
when a single cell is being evaluated by the piece of
your
formula extracted below:

*(TRIM($H$2:$H$12000)=""))

Does this equate to ... TRIM cell (remove spaces) ...
once
done ... if remainder is an EMPTY cell (the double
quote "") ... TRUE? ... Otherwise FALSE because there
would be visible data remaining in the cell???

Am I anywhere close???

Thanks for sticking with me as I already see this as a
valuable function ... Kha



-----Original Message-----
Hi
do you mean:
=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$1200 0=$BG2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)*(TRIM
($H$2:$H$12000)
=""))*(TRIM($L$2:$L$12000)="")))

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
.. .
Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) &
2
Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG 2)
*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2
other
conditions (for Cols H & L) where Col contains data,
empty
cells (or spaces) & I wish empty cells or spaces to
be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha






-----Original Message-----
Hi
you may post your current formula. As an initial
idea:
use TRIM on the
ranges

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb
im
Newsbeitrag
...
Excel 2000 ... I have data I copy/paste to Excel
from
an
Access query (I know nothing about Access so I
munipulate
data in Excel) ... My data consist of 40 columns
by
10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns
(you
just gotta love this function).

3 Columns contain all data while the other 3
Columns
contain Data &/or empty cells or cells
with "spaces" ...
(at least I assume "spaces" because they appear
empty,
but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE
when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to
accommodate
conditions for those 3 Columns that contain data,
&/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of
this
function ... Kha



.



.



.



.

Hi
thanks for your thanks and comments :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Ken wrote:
Frank ...

I know this has been a relatively long thread, but I have
gotten an education from this today & hopefully, a few
others have as well. SUMPRODUCT is a great function.

Above said ... My Thanks ... This is one Excel user that
sincerely appreciates the knowledge & support that you,
other MVPs, & the general Excel population of Users bring
to these boards ... Kha