SUMPRODUCT on an entire column?
 

Hi all,
I'm trying to use SUMPRODUCT to calculate the number of records that
match some creteria.

The code I used in the cell is:

=SUMPRODUCT(--(A1:A100="T"); --(C1:C100="umts"))

But this code check only first 100 rows. Is it possible to check the
entire column?

Trying with

=SUMPRODUCT(--(A:A="T"); --(C:C="umts"))

doesn't work. Reading old posts, I learned that SUMPRODUCT works only
on a discrete range on rows.

But the number of rows to check changes dinamically... so I don't know
the end of the range.

Using

=SUMPRODUCT(--(A1:A65536="T"); --(C1:C65536="umts"))

could work, but what happen if I delete some rows? The range become
smaller?

I thought:

On a cell, I calculate the number of elements to check, with
=COUNTA(A:A)
and maybe is possible to use this value to dynamically create the range
to check in SUMPRODUCT... but I don't know what to do :-(

Could you please help me with this?

Thanks a lot,
Tanilo

SUMPRODUCT on an entire column?
 

=SUMPRODUCT(--(Indirect("A1:A65535")="T"); --(Indirect("C1:C65535")="umts"))

will work as long as you don't use the very last row. However, if you do
this very much, you will get a huge performance hit.

--
Regards,
Tom Ogilvy


" wrote:

Hi all,
I'm trying to use SUMPRODUCT to calculate the number of records that
match some creteria.

The code I used in the cell is:

=SUMPRODUCT(--(A1:A100="T"); --(C1:C100="umts"))

But this code check only first 100 rows. Is it possible to check the
entire column?

Trying with

=SUMPRODUCT(--(A:A="T"); --(C:C="umts"))

doesn't work. Reading old posts, I learned that SUMPRODUCT works only
on a discrete range on rows.

But the number of rows to check changes dinamically... so I don't know
the end of the range.

Using

=SUMPRODUCT(--(A1:A65536="T"); --(C1:C65536="umts"))

could work, but what happen if I delete some rows? The range become
smaller?

I thought:

On a cell, I calculate the number of elements to check, with
=COUNTA(A:A)
and maybe is possible to use this value to dynamically create the range
to check in SUMPRODUCT... but I don't know what to do :-(

Could you please help me with this?

Thanks a lot,
Tanilo

SUMPRODUCT on an entire column?
 

Tom Ogilvy ha scritto:

=SUMPRODUCT(--(Indirect("A1:A65535")="T"); --(Indirect("C1:C65535")="umts"))

Thanks Tom, this works great :-)

However, if you do
this very much, you will get a huge performance hit.

You're right... I see that :-(


Thanks again,
Ciao,
Tanilo