Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct entire month from mmddyyy? | Excel Discussion (Misc queries) | |||
sumproduct calculation does not calculate entire range | Excel Discussion (Misc queries) | |||
If data in one column, take date, add 2 days, and turn the entire column a color... | Excel Discussion (Misc queries) | |||
my column is sorted in two sections. How do I sort entire column? | Excel Discussion (Misc queries) | |||
Find Column heading and then Delete entire column | Excel Programming |