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