row number question in an array

S

SD

Hi there,

I have a forumula

=SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
INJ'!$H$2:$H$55="30000001PC",1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))

My problem is: because it is an array the row number 55 needs be the last
line (ie there cant be any blanks after the last bit of data). This formula
references a query so the number of rows changes daily, one day it might be
70 next 55. How can I get this to reference the exact number of rows in
"scheduled inj "worksheets.

I have a row count set up but need this formula to reference it.

cheers

SD
 
B

Biff

Hi!
My problem is: because it is an array the row number 55 needs be the last
line (ie there cant be any blanks after the last bit of data).
Click to expand...

I don't understand your reasoning, but, try this:

A1 = row count

Normally entered, not an array:

=SUMPRODUCT(--(INDIRECT("'SCHEDULED
INJ'!AL2:AL"&A1)=D3),--(INDIRECT("'SCHEDULED
INJ'!H2:H"&A1)="30000001PC"),INDIRECT("'SCHEDULED INJ'!AM2:AM"&A1))

Biff
 
B

Biff

Hi!

Each cell in the arrays:

AL2:ALn=D3
H2:Hn=30000001PC

will return either TRUE or FALSE.

The "--" converts those to either 1 for TRUE or 0 for FALSE.

Then you end up having all 3 arrays multiplied together then summed for the
final result:

1*0*10=0
1*1*20=20
0*1*10=0
0*0*20=0

Result = 20

Biff
 
A

andy62

I checked Chip's website and have used his formula for creating a dynamic
named range. But I can't find any way to do this: when I used a dynamic
named range in a data validation, I would like the items to appear in
alphabetical order (even though the items in the list are not). Is there any
version of the dynamic named range formula that would alphabetize the list?
Thanks in advance.
 
A

andy62

Sorry to appear helpless, but I don't see anything on her site or in the
zipped file that addresses alphabeticizing the items in the drop-down. But
I'm encouraged to learn it might be possible!
 
B

Biff

Well, the idea is that you can't alphabetize the drop down list itself.
You'd have to create a sublist that is alphabetized and then use that list
as the source for the drop down list.

Biff
 
H

How can I alphabetize a list of names in

SD said:
Hi there,

I have a forumula

=SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
INJ'!$H$2:$H$55="30000001PC",1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))

My problem is: because it is an array the row number 55 needs be the last
line (ie there cant be any blanks after the last bit of data). This formula
references a query so the number of rows changes daily, one day it might be
70 next 55. How can I get this to reference the exact number of rows in
"scheduled inj "worksheets.

I have a row count set up but need this formula to reference it.

cheers

SD
Click to expand...
 
B

Bob Phillips

=SUM(IF((OFFSET('SCHEDULED INJ'!$AL$2,,,COUNTA('SCHEDULED
INJ'!$AL:$AL),1)=D3)*(OFFSET('SCHEDULED INJ'!$H$2,,,COUNTA('SCHEDULED
INJ'!$AL:$AL),1)="30000001PC"),(OFFSET('SCHEDULED
INJ'!$AM$2,,,COUNTA('SCHEDULED INJ'!$AL:$AL),1))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"How can I alphabetize a list of names in" <How can I alphabetize a list of
names [email protected]> wrote in message
 
Top