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