« Charting: Editing charts : part 2 | Main | Creating Custom Cell Styles »
December 09, 2004
Basic functions : SUMIF
This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.The SUMIF function takes two or three arguments.
For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.
In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10
Posted by Dave at December 9, 2004 04:17 AM
Comments
Hi,
Very helpfull, thanks. And how would you generate a vertical array with 'Tom', 'Dave' and 'Jim' automatically?
Posted by: Stef at December 11, 2004 11:52 PM
To the previous poster: You use dollar-sign in the function specification, of course:-)
Posted by: T Petersen at January 14, 2005 10:52 AM
You can use the Standard Filter to remove duplicates.
Select the range
Use Data -> Filter -> Standard Filter
Set test that is always true
Select More
Select Copy results to
Select No duplication
Posted by: Zak Greant at March 23, 2005 09:35 PM
That's very helpfull, thanks. Is it possible to use a slightly more complex expression such as defining a range like between 10 and 25 (?10< & <25) for the criteria, rather than just one value?
Posted by: Tony at October 9, 2005 03:01 AM
I'm new to openoffice and I'm trying to use the SUMIF function to sum numbers based on year to date. I have tried the following: =SUMIF($A3:$A18;">DATE(4;12;31)";B3:B18) where column A is a list of dates and column B has the values. I am getting errors can you help ?
Posted by: Tim at October 27, 2005 03:30 AM
Tim,
I believe the formula should be
=SUMIF($A3:$A18;">" & DATE(4;12;31);B3:B18)
Thanks for stopping by!
Dave
Posted by: Dave at October 27, 2005 08:35 AM
When I try to extend your example to include another column to be summed I get Err:504
sumif(B3:B8;"="&B10;C3:D89)
How do I get this to work?
Thanks
Posted by: Lou Miller at January 14, 2006 11:49 AM
Why would
=SUMIF(I3:I4;"="&G3;E3:E4)
think none of the text values are equal
=IF(G3=I3;TRUE();FALSE())
does believe the values are equal?
Posted by: Michael at January 14, 2006 06:53 PM
Hi,
looks like you might help me?!
I try to sumup times releated to entries in two columns. If cell Ex contains "Frank" and cell Nx contains "Anja" then the times quoted in Px should be summarized. I always got Err508:
=SUMIF(((E2:E5000;"Frank")AND(N2:N5000;"Anja"));P2:P5000)
Can you help me. Thanks,
Frank
Posted by: Frank at February 13, 2006 05:57 AM
how do I define a range (e.g., to convert monthly data to quarterly):
this does not work (either with "AND" nor with "&&"):
=SUMIF($A2:$A60;(">" & DATE(I2;1+(J2-1)*3;1)) AND ("<" & EOMONTH(DATE(I2;1+(J2-1)*3;1);3));$G2:$G60)
here
A contains date
I contains year
J contains quarter number
G contains data to sum
replacing "AND" with "&" returns 0.
Posted by: Anonymous at February 16, 2006 05:13 PM