SUM DATA RANGE

R

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

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

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))))
 
A

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

T. Valko

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top