| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 . |
|
#4
|
|||
|
|||
|
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 . |
|
#5
|
|||
|
|||
|
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 . . |
|
#6
|
|||
|
|||
|
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 . . |
|
#7
|
|||
|
|||
|
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 . . . |
|
#8
|
|||
|
|||
|
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 . . . |
|
#9
|
|||
|
|||
|
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 . . . . |
|
#10
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trendlines to ignore empty cells | Charts and Charting in Excel | |||
| make a cell empty based on condition | Charts and Charting in Excel | |||
| linking cells in Excel 2003. How to not truncate to 255 characters. | Excel Discussion (Misc queries) | |||
| Empty Cells, Spaces, Cond Format? | Excel Discussion (Misc queries) | |||
| empty cells in ranges | Excel Discussion (Misc queries) | |||
Linear Mode
