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.

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
---
"roy.okinawa" wrote:
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.

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:

InsertNameDefine
Name: rng1
Refers to:

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

Name: rng2
Refers to:

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

Then your formula becomes:

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


--
Biff
Microsoft Excel MVP


"roy.okinawa" wrote in message
...
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.

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

"roy.okinawa" wrote in message
...
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.

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

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
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

"roy.okinawa" wrote in message
...
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.