software

=SUMPRODUCT(--((John)=$A5))
Defined field John = 'Access Data'!B$1:B$65000

This works, however I don't want it to go through 65000 each time, only the
number of rows that have data, hence tried =SUMPRODUCT(--(('Access
Data'!B$1:aaaa)=$A5)) where aaaa is defined as a cell with the count of rows,
but Excel returns #Value.

Is there any way to do this.
PS. Need to use SUMPRODUCT as the full statement is =IF($D6lt;gt;quot;
quot;,SUMPRODUCT(--(SEVERITY=$A6),--(BREACHYORN=quot;NOBREACHquot;) (BREACHYORN =
quot;NOSLAquot;),--(ROLL_MONTH_NUM = 1)),quot; quot;)

Try this

=SUMPRODUCT(--(INDIRECT(quot;'Access Data'!B$1:Bquot;amp;A1)=$A5))

but why don't you just use a dynamic name for John etc.?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Paul Dennisquot; gt; wrote in message
...
gt; =SUMPRODUCT(--((John)=$A5))
gt; Defined field John = 'Access Data'!B$1:B$65000
gt;
gt; This works, however I don't want it to go through 65000 each time, only
the
gt; number of rows that have data, hence tried =SUMPRODUCT(--(('Access
gt; Data'!B$1:aaaa)=$A5)) where aaaa is defined as a cell with the count of
rows,
gt; but Excel returns #Value.
gt;
gt; Is there any way to do this.
gt; PS. Need to use SUMPRODUCT as the full statement is =IF($D6lt;gt;quot;
gt; quot;,SUMPRODUCT(--(SEVERITY=$A6),--(BREACHYORN=quot;NOBREACHquot;) (BREACHYORN =
gt; quot;NOSLAquot;),--(ROLL_MONTH_NUM = 1)),quot; quot;)
You should be able to adapt this for your needs:

JobTitle=OFFSET(Applicants!$P$3,,,COUNTA(Applicants!$P:$P)-1)
Store=OFFSET(Applicants!$L$3,,,COUNTA(Applicants!$L:$L)-1)
UpdDate=OFFSET(Applicants!$A$3,,,COUNTA(Applicants!$A:$A)-1)

These named formulas are dynamic (as rows are inserted or deleted, the range
changes.) Then I use the following formula:

=SUMPRODUCT(--(Store=$A6),--(JobTitle=H$2), --(UpdDategt;=$J$4))

--
Kevin Vaughnquot;Paul Dennisquot; wrote:

gt; =SUMPRODUCT(--((John)=$A5))
gt; Defined field John = 'Access Data'!B$1:B$65000
gt;
gt; This works, however I don't want it to go through 65000 each time, only the
gt; number of rows that have data, hence tried =SUMPRODUCT(--(('Access
gt; Data'!B$1:aaaa)=$A5)) where aaaa is defined as a cell with the count of rows,
gt; but Excel returns #Value.
gt;
gt; Is there any way to do this.
gt; PS. Need to use SUMPRODUCT as the full statement is =IF($D6lt;gt;quot;
gt; quot;,SUMPRODUCT(--(SEVERITY=$A6),--(BREACHYORN=quot;NOBREACHquot;) (BREACHYORN =
gt; quot;NOSLAquot;),--(ROLL_MONTH_NUM = 1)),quot; quot;)

Many thanks, offset is greate, however one problem, using the example A is
alway full, however L will sometime be empty, hence it causes problems using
SUMPRODUCT. any ideas?

quot;Kevin Vaughnquot; wrote:

gt; You should be able to adapt this for your needs:
gt;
gt; JobTitle=OFFSET(Applicants!$P$3,,,COUNTA(Applicants!$P:$P)-1)
gt; Store=OFFSET(Applicants!$L$3,,,COUNTA(Applicants!$L:$L)-1)
gt; UpdDate=OFFSET(Applicants!$A$3,,,COUNTA(Applicants!$A:$A)-1)
gt;
gt; These named formulas are dynamic (as rows are inserted or deleted, the range
gt; changes.) Then I use the following formula:
gt;
gt; =SUMPRODUCT(--(Store=$A6),--(JobTitle=H$2), --(UpdDategt;=$J$4))
gt;
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Paul Dennisquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--((John)=$A5))
gt; gt; Defined field John = 'Access Data'!B$1:B$65000
gt; gt;
gt; gt; This works, however I don't want it to go through 65000 each time, only the
gt; gt; number of rows that have data, hence tried =SUMPRODUCT(--(('Access
gt; gt; Data'!B$1:aaaa)=$A5)) where aaaa is defined as a cell with the count of rows,
gt; gt; but Excel returns #Value.
gt; gt;
gt; gt; Is there any way to do this.
gt; gt; PS. Need to use SUMPRODUCT as the full statement is =IF($D6lt;gt;quot;
gt; gt; quot;,SUMPRODUCT(--(SEVERITY=$A6),--(BREACHYORN=quot;NOBREACHquot;) (BREACHYORN =
gt; gt; quot;NOSLAquot;),--(ROLL_MONTH_NUM = 1)),quot; quot;)

I did think about this possibility after I posted. I am lucky that all the
columns I am using are full. However, I did try something that I thought
might work, and it did. What I did was for Store, instead of counting column
L, I counted column A, and it worked. I used cntl-g to go to Store, and all
of the appropriate data was highlighted and my formula continued to work. My
new defined formula for store looks like:

Store=OFFSET(Applicants!$L$3,,,COUNTA(Applicants!$A:$A)-1)

HTH
--
Kevin Vaughnquot;Paul Dennisquot; wrote:

gt; Many thanks, offset is greate, however one problem, using the example A is
gt; alway full, however L will sometime be empty, hence it causes problems using
gt; SUMPRODUCT. any ideas?
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; You should be able to adapt this for your needs:
gt; gt;
gt; gt; JobTitle=OFFSET(Applicants!$P$3,,,COUNTA(Applicants!$P:$P)-1)
gt; gt; Store=OFFSET(Applicants!$L$3,,,COUNTA(Applicants!$L:$L)-1)
gt; gt; UpdDate=OFFSET(Applicants!$A$3,,,COUNTA(Applicants!$A:$A)-1)
gt; gt;
gt; gt; These named formulas are dynamic (as rows are inserted or deleted, the range
gt; gt; changes.) Then I use the following formula:
gt; gt;
gt; gt; =SUMPRODUCT(--(Store=$A6),--(JobTitle=H$2), --(UpdDategt;=$J$4))
gt; gt;
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Paul Dennisquot; wrote:
gt; gt;
gt; gt; gt; =SUMPRODUCT(--((John)=$A5))
gt; gt; gt; Defined field John = 'Access Data'!B$1:B$65000
gt; gt; gt;
gt; gt; gt; This works, however I don't want it to go through 65000 each time, only the
gt; gt; gt; number of rows that have data, hence tried =SUMPRODUCT(--(('Access
gt; gt; gt; Data'!B$1:aaaa)=$A5)) where aaaa is defined as a cell with the count of rows,
gt; gt; gt; but Excel returns #Value.
gt; gt; gt;
gt; gt; gt; Is there any way to do this.
gt; gt; gt; PS. Need to use SUMPRODUCT as the full statement is =IF($D6lt;gt;quot;
gt; gt; gt; quot;,SUMPRODUCT(--(SEVERITY=$A6),--(BREACHYORN=quot;NOBREACHquot;) (BREACHYORN =
gt; gt; gt; quot;NOSLAquot;),--(ROLL_MONTH_NUM = 1)),quot; quot;)