Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT(--(('Access Data'!B$1:aaaa)=$A5))
=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))," ") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT(--(('Access Data'!B$1:aaaa)=$A5))
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) "Paul Dennis" wrote in message ... =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))," ") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT(--(('Access Data'!B$1:aaaa)=$A5))
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)) -- Kevin Vaughn "Paul Dennis" wrote: =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))," ") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT(--(('Access Data'!B$1:aaaa)=$A5))
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? "Kevin Vaughn" wrote: 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)) -- Kevin Vaughn "Paul Dennis" wrote: =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))," ") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT(--(('Access Data'!B$1:aaaa)=$A5))
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 Vaughn "Paul Dennis" wrote: 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? "Kevin Vaughn" wrote: 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)) -- Kevin Vaughn "Paul Dennis" wrote: =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))," ") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot save Project file as MS Access file | Excel Discussion (Misc queries) | |||
Linking Large Access Table into Excel | Links and Linking in Excel | |||
Access Excel Linked Text and Number Issues | Excel Discussion (Misc queries) | |||
Excel and Access talking | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |