=SUMPRODUCT(--(('Access Data'!B$1:aaaa)=$A5))

[Guest]

=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($D6<>"
",SUMPRODUCT(--(SEVERITY=$A6),--(BREACHYORN="NOBREACH")++(BREACHYORN =
"NOSLA"),--(ROLL_MONTH_NUM = 1))," ")

 

[Bob Phillips]

Try this

=SUMPRODUCT(--(INDIRECT("'Access Data'!B$1:B"&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)

 

[Guest]

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), --(UpdDate>=$J$4))

 

[Guest]

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?

 

[Guest]

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