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