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
|