| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif? array enter?
Hi all,
I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
How many rows of data do you have?
This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
T,
Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
But \ in stead of ; is new for me.
Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
T,
How if I want to sum in row 15 those cells for which in their column one or more cells in the range AO20:AO40 is not empty. I tried but did not succeed, feel dumb. Again your help will be appreciated. Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
T,
Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
Not sure I understand you.
Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
I want to sum of row10 (between say colums J and T those cells that are in a
column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
Ok, I think I understand.
.......A.......B......C ......10.....10.....10 ........................... .......x................x .......x................x .........................x .......x.................. The result should be 20, sum of A+C Try this: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1))0),J10:T10) I'm assuming the cells are *empty* and do not contain formula blanks. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... I want to sum of row10 (between say colums J and T those cells that are in a column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
T,
Thanks. Can't we work in this case with something like {1;1;1;1;1} or {1,1,1,1,1} ? Second, if there are formula blanks - quite possible, didn't thought of them - what kind of formula do I need to overcome that? Third, (hope you don't mind me asking so much) please explain: OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1) Is the ,, the same as ,0, ? Thanks in advance. Jack. "T. Valko" schreef in bericht ... Ok, I think I understand. ......A.......B......C .....10.....10.....10 .......................... ......x................x ......x................x ........................x ......x.................. The result should be 20, sum of A+C Try this: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1))0),J10:T10) I'm assuming the cells are *empty* and do not contain formula blanks. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... I want to sum of row10 (between say colums J and T those cells that are in a column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
Can't we work in this case with something
like {1;1;1;1;1} or{1,1,1,1,1} ? You don't need anything like that for this application. if there are formula blanks - quite possible, what kind of formula do I need to overcome that? What kind of data is in the range J20:T26, text? Numbers? Both? Is the ,, the same as ,0, ? Yes. That means we want to offset the range by 0 rows. Or, to put it another way, we don't want to offset the rows of the range. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Thanks. Can't we work in this case with something like {1;1;1;1;1} or {1,1,1,1,1} ? Second, if there are formula blanks - quite possible, didn't thought of them - what kind of formula do I need to overcome that? Third, (hope you don't mind me asking so much) please explain: OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1) Is the ,, the same as ,0, ? Thanks in advance. Jack. "T. Valko" schreef in bericht ... Ok, I think I understand. ......A.......B......C .....10.....10.....10 .......................... ......x................x ......x................x ........................x ......x.................. The result should be 20, sum of A+C Try this: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1))0),J10:T10) I'm assuming the cells are *empty* and do not contain formula blanks. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... I want to sum of row10 (between say colums J and T those cells that are in a column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
Data in the range J20:T26 and also in J10:T10 can be anything, text,
numbers, formulas. What if I only want to take in account those cells in J20:T26 that contain numbers (no text or formula) and what if numeric results of formula also are to be taken in account? Jack. "T. Valko" schreef in bericht ... Can't we work in this case with something like {1;1;1;1;1} or{1,1,1,1,1} ? You don't need anything like that for this application. if there are formula blanks - quite possible, what kind of formula do I need to overcome that? What kind of data is in the range J20:T26, text? Numbers? Both? Is the ,, the same as ,0, ? Yes. That means we want to offset the range by 0 rows. Or, to put it another way, we don't want to offset the rows of the range. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Thanks. Can't we work in this case with something like {1;1;1;1;1} or {1,1,1,1,1} ? Second, if there are formula blanks - quite possible, didn't thought of them - what kind of formula do I need to overcome that? Third, (hope you don't mind me asking so much) please explain: OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1) Is the ,, the same as ,0, ? Thanks in advance. Jack. "T. Valko" schreef in bericht ... Ok, I think I understand. ......A.......B......C .....10.....10.....10 .......................... ......x................x ......x................x ........................x ......x.................. The result should be 20, sum of A+C Try this: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1))0),J10:T10) I'm assuming the cells are *empty* and do not contain formula blanks. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... I want to sum of row10 (between say colums J and T those cells that are in a column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
With all of those different possibilities I think you're best option would
be to use a row of intermediate calculations. Data in the range J20:T26 and also in J10:T10 For example, in J28 enter a formula that does the calculation based on J20:J26 then copy that formula across to T28. Then, do the calculation on J10:T10 based on the intermediate formulas. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Data in the range J20:T26 and also in J10:T10 can be anything, text, numbers, formulas. What if I only want to take in account those cells in J20:T26 that contain numbers (no text or formula) and what if numeric results of formula also are to be taken in account? Jack. "T. Valko" schreef in bericht ... Can't we work in this case with something like {1;1;1;1;1} or{1,1,1,1,1} ? You don't need anything like that for this application. if there are formula blanks - quite possible, what kind of formula do I need to overcome that? What kind of data is in the range J20:T26, text? Numbers? Both? Is the ,, the same as ,0, ? Yes. That means we want to offset the range by 0 rows. Or, to put it another way, we don't want to offset the rows of the range. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Thanks. Can't we work in this case with something like {1;1;1;1;1} or {1,1,1,1,1} ? Second, if there are formula blanks - quite possible, didn't thought of them - what kind of formula do I need to overcome that? Third, (hope you don't mind me asking so much) please explain: OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1) Is the ,, the same as ,0, ? Thanks in advance. Jack. "T. Valko" schreef in bericht ... Ok, I think I understand. ......A.......B......C .....10.....10.....10 .......................... ......x................x ......x................x ........................x ......x.................. The result should be 20, sum of A+C Try this: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1))0),J10:T10) I'm assuming the cells are *empty* and do not contain formula blanks. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... I want to sum of row10 (between say colums J and T those cells that are in a column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
Thanks T.
Jack. "T. Valko" schreef in bericht ... With all of those different possibilities I think you're best option would be to use a row of intermediate calculations. Data in the range J20:T26 and also in J10:T10 For example, in J28 enter a formula that does the calculation based on J20:J26 then copy that formula across to T28. Then, do the calculation on J10:T10 based on the intermediate formulas. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Data in the range J20:T26 and also in J10:T10 can be anything, text, numbers, formulas. What if I only want to take in account those cells in J20:T26 that contain numbers (no text or formula) and what if numeric results of formula also are to be taken in account? Jack. "T. Valko" schreef in bericht ... Can't we work in this case with something like {1;1;1;1;1} or{1,1,1,1,1} ? You don't need anything like that for this application. if there are formula blanks - quite possible, what kind of formula do I need to overcome that? What kind of data is in the range J20:T26, text? Numbers? Both? Is the ,, the same as ,0, ? Yes. That means we want to offset the range by 0 rows. Or, to put it another way, we don't want to offset the rows of the range. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Thanks. Can't we work in this case with something like {1;1;1;1;1} or {1,1,1,1,1} ? Second, if there are formula blanks - quite possible, didn't thought of them - what kind of formula do I need to overcome that? Third, (hope you don't mind me asking so much) please explain: OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1) Is the ,, the same as ,0, ? Thanks in advance. Jack. "T. Valko" schreef in bericht ... Ok, I think I understand. ......A.......B......C .....10.....10.....10 .......................... ......x................x ......x................x ........................x ......x.................. The result should be 20, sum of A+C Try this: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1))0),J10:T10) I'm assuming the cells are *empty* and do not contain formula blanks. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... I want to sum of row10 (between say colums J and T those cells that are in a column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif? array enter?
You're welcome!
-- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Thanks T. Jack. "T. Valko" schreef in bericht ... With all of those different possibilities I think you're best option would be to use a row of intermediate calculations. Data in the range J20:T26 and also in J10:T10 For example, in J28 enter a formula that does the calculation based on J20:J26 then copy that formula across to T28. Then, do the calculation on J10:T10 based on the intermediate formulas. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Data in the range J20:T26 and also in J10:T10 can be anything, text, numbers, formulas. What if I only want to take in account those cells in J20:T26 that contain numbers (no text or formula) and what if numeric results of formula also are to be taken in account? Jack. "T. Valko" schreef in bericht ... Can't we work in this case with something like {1;1;1;1;1} or{1,1,1,1,1} ? You don't need anything like that for this application. if there are formula blanks - quite possible, what kind of formula do I need to overcome that? What kind of data is in the range J20:T26, text? Numbers? Both? Is the ,, the same as ,0, ? Yes. That means we want to offset the range by 0 rows. Or, to put it another way, we don't want to offset the rows of the range. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Thanks. Can't we work in this case with something like {1;1;1;1;1} or {1,1,1,1,1} ? Second, if there are formula blanks - quite possible, didn't thought of them - what kind of formula do I need to overcome that? Third, (hope you don't mind me asking so much) please explain: OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1) Is the ,, the same as ,0, ? Thanks in advance. Jack. "T. Valko" schreef in bericht ... Ok, I think I understand. ......A.......B......C .....10.....10.....10 .......................... ......x................x ......x................x ........................x ......x.................. The result should be 20, sum of A+C Try this: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(J20:T26,,COLUMN(J20:T26)-COLUMN(J20),ROWS(J20:T26),1))0),J10:T10) I'm assuming the cells are *empty* and do not contain formula blanks. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... I want to sum of row10 (between say colums J and T those cells that are in a column of which any of the cells (one or more) from row 22 to row 26 is not empty. So more or less the reverse (ore the transpose? sorry if my English is not very clear) of my first question. Jack. "T. Valko" schreef in bericht ... Not sure I understand you. Do you want to check the 15th row of the range AO20:AT40 and if any cell on that row is not empty then return the corresponding value from column F? The 15th row of that range would be AO34:AT34 and the corresponding value would be in cell F34. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ). Jack. "T. Valko" schreef in bericht ... But \ in stead of ; is new for me. Can I excpect that also in other cases? I have no idea! I work strictly with U.S. English versions of Excel. In U.S. English versions semi-colons denote a vertical array: {1;1;1;1;1}. Commas are used for a horizontal array: {1,1,1,1,1}. -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... T, Works like a charm, many thanks. Never thought of MMULT and certainly not of using it in this way. Very useful if a column in the range is to be excluded, e.g. with (1;1;1;0;1} By the way, my (Dutch) version of Excel2k shows {1\1\1\1\1}in stead of {1;1;1;1;1}. I know the difference between European and USA version with respect to ; in stead of , in formulas. But \ in stead of ; is new for me. Can I excpect that also in other cases? Jack. "T. Valko" schreef in bericht ... How many rows of data do you have? This is limited to no more than 5461 rows. =SUMPRODUCT(--(MMULT(--(AK2:AO10<""),{1;1;1;1;1})0),F2:F10) -- Biff Microsoft Excel MVP "Jack Sons" wrote in message ... Hi all, I want to sum in column F those cells for which in their row one or more cells in the range AK:AO is not empty. I tried but did not succeed. Possibly whith an array entered formula? (not VBA) Your help will be appreciated. Jack Sons The Netherlands |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
| I can't enter an array in Excel 2007 | Excel Discussion (Misc queries) | |||
| I can't enter an array in Excel 2007 | Excel Worksheet Functions | |||
| enter doesn't work with sumif | Excel Worksheet Functions | |||
| Proper way to enter array formula | Excel Worksheet Functions | |||
Linear Mode
