Re: [libreoffice-users] Calc

At 23:57 06/09/2011 +0200, Klaus Friis Østergaard wrote:
> Search criteria in SUM.IF
>
> I have a list with two columns:
>
> A        B
> Type   Amount
> a        1000
> b         500
> c         250
> a         200
> a         100
> b         300
> ...
>
> in real life with more than 50 types.  I want to 
> make a table that summarizes the total amount of 
> types.  I wanted to make a two column table again like this:
>
> X        Y
> Type   total_amount
> a        1300
> b         800
> c         250
>
> I was trying to use sum.if function.  but how do 
> I make the criteria string in the sum.if Depending on the value in column X
>
> a   =sum.if(A1:A10;"=a";B1:B10)  but this is static,
>
> how can I make this as an reference with 
> CONCATENATE like =sum.if(A1:A10,CONCATENATE('"=';TEXT(X2);'"');B1:B10 )

I think you are making heavy weather of this.  If 
the criterion is equality, it does not need to be 
mentioned: you need just to indicate the cell 
containing the relevant value.  Try:
 =SUMIF(A1:A10;X2;B1:B10)
Since you need to fill this down the Y column, 
you will need to freeze the row references to A and B, of course:
 =SUMIF(A$1:A$10;X2;B$1:B$10)

I trust this helps.

Brian Barker


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted