Custom Function Desired
 

I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3)

How to give it a correct and complete form?

Thanx!

Custom Function Desired
 

On Dec 27, 8:54*am, FARAZ QURESHI
wrote:
I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3)

How to give it a correct and complete form?

Thanx!

Hi Faraz,

you dont have to create a custom function.

Assume the following
-----------------
Array1 = A1:A10
Array2 = B1:B10
Array3 = C1:C10
STRING = D1
Limit = D2



1. Write the following code into a new cell. (Do not press ENTER)
=SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10)

2. Press CTRL + SHIFT + ENTER.
this will encapsulate it in { }. Now it is an array and should work
for you.
Note: Make sure that the array size same everywhere.

HTH
Joe

Custom Function Desired
 

I know how to determine such a result. But I want to create a custom function!
A further refined formula is the one already presented in the body!

"Joe" wrote:

On Dec 27, 8:54 am, FARAZ QURESHI
wrote:
I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3)

How to give it a correct and complete form?

Thanx!

Hi Faraz,

you dont have to create a custom function.

Assume the following
-----------------
Array1 = A1:A10
Array2 = B1:B10
Array3 = C1:C10
STRING = D1
Limit = D2



1. Write the following code into a new cell. (Do not press ENTER)
=SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10)

2. Press CTRL + SHIFT + ENTER.
this will encapsulate it in { }. Now it is an array and should work
for you.
Note: Make sure that the array size same everywhere.

HTH
Joe

Custom Function Desired
 

See response in excel.misc. You know better than to multi-post by now!

--
HTH

Bob

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

"Joe" wrote in message
...
On Dec 27, 8:54 am, FARAZ QURESHI
wrote:
I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3)

How to give it a correct and complete form?

Thanx!

Hi Faraz,

you dont have to create a custom function.

Assume the following
-----------------
Array1 = A1:A10
Array2 = B1:B10
Array3 = C1:C10
STRING = D1
Limit = D2



1. Write the following code into a new cell. (Do not press ENTER)
=SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10)

2. Press CTRL + SHIFT + ENTER.
this will encapsulate it in { }. Now it is an array and should work
for you.
Note: Make sure that the array size same everywhere.

HTH
Joe

Custom Function Desired
 

THANX BOB!!!!!!!!!!!!!

U really made my problems solved!

I am really sorry for double posting but I was actually receiving no
response and had presumed that it might have been overlooked!

Thanx again buddy!

"Bob Phillips" wrote:

See response in excel.misc. You know better than to multi-post by now!

--
HTH

Bob

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

"Joe" wrote in message
...
On Dec 27, 8:54 am, FARAZ QURESHI
wrote:
I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3)

How to give it a correct and complete form?

Thanx!

Hi Faraz,

you dont have to create a custom function.

Assume the following
-----------------
Array1 = A1:A10
Array2 = B1:B10
Array3 = C1:C10
STRING = D1
Limit = D2



1. Write the following code into a new cell. (Do not press ENTER)
=SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10)

2. Press CTRL + SHIFT + ENTER.
this will encapsulate it in { }. Now it is an array and should work
for you.
Note: Make sure that the array size same everywhere.

HTH
Joe

Custom Function Desired
 

Faraz,

It generally won't get overlooked as most of us will check out all of the
major Excel groups. If it isn't getting answered it will probably mean that
there are few people online, so just be a bit patient.

--
---
HTH

Bob


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



"FARAZ QURESHI" wrote in message
...
THANX BOB!!!!!!!!!!!!!

U really made my problems solved!

I am really sorry for double posting but I was actually receiving no
response and had presumed that it might have been overlooked!

Thanx again buddy!

"Bob Phillips" wrote:

See response in excel.misc. You know better than to multi-post by now!

--
HTH

Bob

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

"Joe" wrote in message
...
On Dec 27, 8:54 am, FARAZ QURESHI
wrote:
I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3)

How to give it a correct and complete form?

Thanx!

Hi Faraz,

you dont have to create a custom function.

Assume the following
-----------------
Array1 = A1:A10
Array2 = B1:B10
Array3 = C1:C10
STRING = D1
Limit = D2



1. Write the following code into a new cell. (Do not press ENTER)
=SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10)

2. Press CTRL + SHIFT + ENTER.
this will encapsulate it in { }. Now it is an array and should work
for you.
Note: Make sure that the array size same everywhere.

HTH
Joe

Custom Function Desired
 

On Dec 27 2007, 3:47*pm, "Bob Phillips" wrote:
Faraz,

It generally won't get overlooked as most of us will check out all of the
major Excel groups. If it isn't getting answered it will probably mean that
there are few people online, so just be a bit patient.

--
---
HTH

Bob

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

"FARAZ QURESHI" wrote in message

...



THANX BOB!!!!!!!!!!!!!

U really made my problems solved!

I am really sorry for double posting but I was actually receiving no
response and had presumed that it might have been overlooked!

Thanx again buddy!

"Bob Phillips" wrote:

See response in excel.misc. You know better than to multi-post by now!

--
HTH

Bob

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

"Joe" wrote in message
....
On Dec 27, 8:54 am, FARAZ QURESHI
wrote:
I want to create a custom function like:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, STRING)
MyFormula=SUMPRODUCT(--(ARRAY1=STRING),--(ARRAY20),ARRAY3)

How to give it a correct and complete form?

Thanx!

Hi Faraz,

you dont have to create a custom function.

Assume the following
-----------------
Array1 = *A1:A10
Array2 = *B1:B10
Array3 = *C1:C10
STRING = *D1
Limit *= *D2

1. Write the following code into a new cell. (Do not press ENTER)
=SUM(IF(A1:A10=D1,"1","0")*IF(B1:B10D2,"1","0")*C 1:C10)

2. Press CTRL + SHIFT + ENTER.
this will encapsulate it in { }. *Now it is an array and should work
for you.
Note: Make sure that the array size same everywhere.

HTH
Joe- Hide quoted text -

- Show quoted text -


Ahh.. That works better.. This is new to me.. I used to the Array
Way..
But what is the significance of "--". Is it that we have to put "--"
before the array comparison ??

Thanks
Joe