SUM DATA RANGE

[roy.okinawa]

Using the below formula as example, is there another formula I can use where
I do not need to put a ending cell to sumproduct. I know I could just put a
larger number in, but this formula is getting info from another worksheet
that has data entered everyday so it keeps growing. As you can see I have it
ending at 2000 but would like it keep formulating without always needing to
put the ending cell.

=SUMPRODUCT(--(TEXT(Overall!E3:E2000,"mmm/yyyy")=TEXT(C2,"mmm/yyyy")),--(ISNUMBER(SEARCH(A11,Overall!C3:C2000))))

Thanks.

 

[Max]

My thoughts would be to just extend the range, but keep it at the smallest
size large enough to cover for say projected data increments over the next
6-12 months? You would need to rough estimate this based on your
circumstances. So possibly, you could extend it to say, row 3000?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[T. Valko]

You can use dynamic ranges.

Is the data in each of the referenced ranges entered in a contiguous block?

If you're able, it's better to use INDEX rather than OFFSET to define
dynamic ranges.

For example:

Insert>Name>Define
Name: rng1
Refers to:

=Overall!$E$3:INDEX(Overall!$E$3:$E$65536,COUNTA(Overall!$E$3:$E$65536))

Name: rng2
Refers to:

=Overall!$C$3:INDEX(Overall!$C$3:$C$65536,COUNTA(Overall!$C$3:$C$65536))

Then your formula becomes:

=SUMPRODUCT(--(TEXT(rng1,"mmm/yyyy")=TEXT(C2,"mmm/yyyy")),--(ISNUMBER(SEARCH(A11,rng2))))

 

[Ashish Mathur]

Hi,

Highlight C3:E2000 and convert it to a list (Ctrl+L). This will male the
ranges "auto expanding". Whenever you add data in the rows below, the range
would automatically keep expanding in the formula. Please ensure that you
do not leave blank rows, blank columns and blank cells anywhere in the
range.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

Note that the List feature is only available in Excel versions 2003 or
greater.