sumif? array enter?

[Jack Sons]

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

 

[T. Valko]

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)

 

[Jack Sons]

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]

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}.

 

[Jack Sons]

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.

 

[Jack Sons]

T,

Sorry, in my previous post read AO20:AT40 (in stead of AO20:AO40 ).

Jack.

 

[T. Valko]

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.

 

[Jack Sons]

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]

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.

 

[Jack Sons]

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]

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.

 

[Jack Sons]

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]

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.

 

[Jack Sons]

Thanks T.

Jack.

With all of those different possibilities I think you're best option would
be to use a row of intermediate calculations.


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.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks T.

Jack.