sumifs in excel 2003

  • Thread starter Thread starter Lok Tak Cheong
  • Start date Start date
L

Lok Tak Cheong

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?
 
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)
 
Try:-

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

Mike
Click to expand...

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 said:
....
would

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

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)}
Click to expand...

And this is a worse syntax error. Maybe

=SUM(IF(AND((A1:A20=1000),(B1:B20=2000)),C1:C20,0))
?
Click to expand...

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.
 
I'm not sure if anyone is still reviewing/responding to this thread but here is my dilemma anyway.

I am using, perhaps, a more advanced usage of the SUMIFS formula. My criteria is a range of dates such as the following:

=SUMIFS($C$4:$AL$4,$C$6:$AL$6,">=10/1/2011",$C$6:$AL$6,"<=9/30/2012")

Note the syntax includes the >= and <= symbols.

I would like to sum the values located on Row 4 that are between the dates noted (on Row 6). This works very well with SUMIFS in Excel 2007, but none of the above suggestions (sumproduct; sum(if(( array) appear to work for a >= or <= symbols in version 2003.

Anyone have a potential solution?
 
A few of us a still around.

Try this:

=SUMPRODUCT($C$4:$AL$4,--($C$6:$AL$6>=--"10/1/2011"),--($C$6:$AL
$6<=--"9/30/2012"))

The double-minus in front of the dates convert those text values into
proper dates, and those in front of the brackets convert the TRUEs and
FALSEs from the comparisons to 1 and 0 respectively.

It is common to put all the criteria first, so another way of writing
this would be:

=SUMPRODUCT(--($C$6:$AL$6>=--"10/1/2011"),--($C$6:$AL
$6<=--"9/30/2012"),$C$4:$AL$4)

and some people like to use this approach for two or more conditions:

=SUMPRODUCT(($C$6:$AL$6>=--"10/1/2011")*($C$6:$AL$6<=--"9/30/2012"),$C
$4:$AL$4)

where the asterisk can be interpreted as AND.

Hope this helps.

Pete
 
Back
Top