I want to make a function in excel 2003 that likes the function "sumifs" in
excel 2007.
Anyone please help.

Not familiar with XL2007 but I believe SUMPRODUCT will provide the equivalent
of SUMIFS.

Can you give an example of what are you trying to do?

"Lok Tak Cheong" wrote:

I want to make a function in excel 2003 that likes the function "sumifs" in
excel 2007.
Anyone please help.

e.g.
Account Cost Center Amount
1000 1000 $500
1000 2000 $1000
1100 1000 $1500
1100 2000 $2000

I want to find out the amount if Accout =1000 and Cost = 2000.
Please note that there may be more than 2 criteria and I want to make a
create a customer function such as sumifs(Criteria_A, Criteria_B, Array
Table, Total)

"Toppers" wrote in message
...
Not familiar with XL2007 but I believe SUMPRODUCT will provide the
equivalent
of SUMIFS.

Can you give an example of what are you trying to do?

"Lok Tak Cheong" wrote:

I want to make a function in excel 2003 that likes the function "sumifs"
in
excel 2007.
Anyone please help.

Try:-

=SUMPRODUCT((A1:A20=1000)*(B1:B20=2000)*(C1:C20))

Mike


"Lok Tak Cheong" wrote:

e.g.
Account Cost Center Amount
1000 1000 $500
1000 2000 $1000
1100 1000 $1500
1100 2000 $2000

I want to find out the amount if Accout =1000 and Cost = 2000.
Please note that there may be more than 2 criteria and I want to make a
create a customer function such as sumifs(Criteria_A, Criteria_B, Array
Table, Total)

"Toppers" wrote in message
...
Not familiar with XL2007 but I believe SUMPRODUCT will provide the
equivalent
of SUMIFS.

Can you give an example of what are you trying to do?

"Lok Tak Cheong" wrote:

I want to make a function in excel 2003 that likes the function "sumifs"
in
excel 2007.
Anyone please help.

Thanks
"Mike H" wrote in message
...
Try:-

=SUMPRODUCT((A1:A20=1000)*(B1:B20=2000)*(C1:C20))

Mike


"Lok Tak Cheong" wrote:

e.g.
Account Cost Center Amount
1000 1000 $500
1000 2000 $1000
1100 1000 $1500
1100 2000 $2000

I want to find out the amount if Accout =1000 and Cost = 2000.
Please note that there may be more than 2 criteria and I want to make a
create a customer function such as sumifs(Criteria_A, Criteria_B, Array
Table, Total)

"Toppers" wrote in message
...
Not familiar with XL2007 but I believe SUMPRODUCT will provide the
equivalent
of SUMIFS.

Can you give an example of what are you trying to do?

"Lok Tak Cheong" wrote:

I want to make a function in excel 2003 that likes the function
"sumifs"
in
excel 2007.
Anyone please help.

On 25 Jun., 10:31, Mike H wrote:
Try:-

=SUMPRODUCT((A1:A20=1000)*(B1:B20=2000)*(C1:C20))

Mike

"Lok Tak Cheong" wrote:
e.g.
Account Cost Center Amount
1000 1000 $500
1000 2000 $1000
1100 1000 $1500
1100 2000 $2000

would

{=sum(if((A1:A20=1000)*(B1:B20=2000));C1:C20;0)} (matrix formula!)

do the same? And why does it give other results as

{=sum(if(and((A1:A20=1000);(B1:B20=2000)));C1:C20; 0)}

?

Regards Olaf

"ergo" wrote...
Mike H wrote:
Try:-

=SUMPRODUCT((A1:A20=1000)*(B1:B20=2000)*(C1:C20) )
....
would

{=sum(if((A1:A20=1000)*(B1:B20=2000));C1:C20;0) } (matrix formula!)

Actually, this is a syntax error even with ; as list separator. Maybe

=SUM(IF((A1:A20=1000)*(B1:B20=2000)),C1:C20,0))


do the same? And why does it give other results as

{=sum(if(and((A1:A20=1000);(B1:B20=2000)));C1:C20 ;0)}

And this is a worse syntax error. Maybe

=SUM(IF(AND((A1:A20=1000),(B1:B20=2000)),C1:C20,0) )

?

Both syntactically invalid with typical English language regional settings
in which comma , is the list separator character. If you're going to post
in English language newsgroups, note this fact.

Mike H's formula and your first formula would give the same results except
when C1:C20 contained any boolean values or nonnumeric text. In that case,
Mike H's formula would produce different numeric results (treating Boolean
TRUEs as if they were 1s) or #VALUE! (caused by nonnumeric text). Your first
formula would skip such values in C1:C20.

Your second formula doesn't do the same thing because AND returns ONE AND
ONLY ONE value as its result. That means

AND((A1:A20=1000),(B1:B20=2000))

is evaluated as

AND(A1=1000,A2=1000,...,A20=1000,B1=2000,B2=2000,. ..,B20=2000)

but (A1:A20=1000)*(B1:B20=2000) returns and array of 1s or 0s depending on
whether the column A and B cells IN EACH ROW SEPARATELY satisfy their
respective criteria. Your second formula would be faster because the first
argument to IF would return FALSE more quickly, so the outer SUM call would
return 0 more quickly. Your formula would only return something other than
zero when EVERY entry in A1:A20 were 1000 AND EVERY entry in B1:B20 were
2000.