Sum values based on certain criteria

[willwall]

Hello
I am trying to get the sum of cells containing certain text. For example, I would like to get the sum of the cells that contain the following text "WILLYS", "GROSS", "SKANSEN"

[TABLE="width: 646"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Bokf.dat[/TD]
[TD]Trans.dat[/TD]
[TD]Text[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2018-05-07[/TD]
[TD]2018-05-06[/TD]
[TD]WILLYS KUNGSGATAN UPPS UPPSALA[/TD]
[TD="align: right"]-2175,49[/TD]
[/TR]
[TR]
[TD]2018-05-07[/TD]
[TD]2018-05-06[/TD]
[TD]VERO MAT CAFE UPPSALA[/TD]
[TD="align: right"]-296,00[/TD]
[/TR]
[TR]
[TD]2018-05-07[/TD]
[TD]2018-05-06[/TD]
[TD]CITY GROSS UPPSALA UPPSALA[/TD]
[TD="align: right"]-39,00[/TD]
[/TR]
[TR]
[TD]2018-05-07[/TD]
[TD]2018-05-06[/TD]
[TD]LINDEX/363 UPPSALA[/TD]
[TD="align: right"]-398,00[/TD]
[/TR]
[TR]
[TD]2018-05-07[/TD]
[TD]2018-05-06[/TD]
[TD]SKOHUSET UPPSALA[/TD]
[TD="align: right"]-398,00[/TD]
[/TR]
[TR]
[TD]2018-05-08[/TD]
[TD]2018-05-08[/TD]
[TD] ensin[/TD]
[TD="align: right"]-1000,00[/TD]
[/TR]
[TR]
[TD]2018-05-10[/TD]
[TD]2018-05-09[/TD]
[TD]APOTEKET BOLANDERNA UPPSALA[/TD]
[TD="align: right"]-246,40[/TD]
[/TR]
[TR]
[TD]2018-05-11[/TD]
[TD]2018-05-11[/TD]
[TD]Test[/TD]
[TD="align: right"]2000,00[/TD]
[/TR]
[TR]
[TD]2018-05-11[/TD]
[TD]2018-05-10[/TD]
[TD]PANDURO HOBBY UPPSALA[/TD]
[TD="align: right"]-299,84[/TD]
[/TR]
[TR]
[TD]2018-05-11[/TD]
[TD]2018-05-10[/TD]
[TD]BRLeksaker F2273 Uppsala S[/TD]
[TD="align: right"]-123,70[/TD]
[/TR]
[TR]
[TD]2018-05-11[/TD]
[TD]2018-05-10[/TD]
[TD]KONDITORI FAGELSANGEN GRA UPPS[/TD]
[TD="align: right"]-120,00[/TD]
[/TR]
[TR]
[TD]2018-05-11[/TD]
[TD]2018-05-09[/TD]
[TD]STORA COOP BOLANDERNA UPPSALA[/TD]
[TD="align: right"]-304,88[/TD]
[/TR]
[TR]
[TD]2018-05-12[/TD]
[TD]2018-05-11[/TD]
[TD]DJURGARDSBRON SJOCAFE STOCKHOL[/TD]
[TD="align: right"]-80,00[/TD]
[/TR]
[TR]
[TD]2018-05-12[/TD]
[TD]2018-05-11[/TD]
[TD]NYBRO GRILLEN STOCKHOLM[/TD]
[TD="align: right"]-359,00[/TD]
[/TR]
[TR]
[TD]2018-05-12[/TD]
[TD]2018-05-11[/TD]
[TD]SKANSEN STOCKHOLM[/TD]
[TD="align: right"]-310,00[/TD]
[/TR]
[TR]
[TD]2018-05-13[/TD]
[TD]2018-05-12[/TD]
[TD]KD APOTEK UPPSALA[/TD]
[TD="align: right"]-416,50[/TD]
[/TR]
[TR]
[TD]2018-05-14[/TD]
[TD]2018-05-13[/TD]
[TD]ICA KRYSSET ALMUNGE ALMUNGE[/TD]
[TD="align: right"]-216,68[/TD]
[/TR]
[TR]
[TD]2018-05-15[/TD]
[TD]2018-05-14[/TD]
[TD]WILLYS KUNGSGATAN UPPS UPPSALA[/TD]
[TD="align: right"]-1191,83[/TD]
[/TR]
[TR]
[TD]2018-05-15[/TD]
[TD]2018-05-14[/TD]
[TD]QPARK ATOS STOCKHOLM[/TD]
[TD="align: right"]-72,00[/TD]
[/TR]
[TR]
[TD]2018-05-16[/TD]
[TD]2018-05-15[/TD]
[TD]FJALLNORA FRILUFTSGARD UPPSALA[/TD]
[TD="align: right"]-62,50[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

You could use SUMIF with a wild card:

=SUMIF($C$2:$C$21,"*WILLYS*",$D$2:$D$21)

Assuming C2:C21 contains the text and D2:D21 is the amount.

This returns -3367.32 in this example.

EDIT: If you want it more dynamic then make some helper cells with the words "WILLYS", "GROSS" & "SKANSEN" in and reference them instead.

I.e. if cell G2 is "SKANSEN" then:
=SUMIF($C$2:$C$21,"*"&G2&"*",$D$2:$D$21)

 

[willwall]

Works perfect, thanks for your help

 

[willwall]

Hello again
Just one more question. This formula returns for example "skansen". =SUMIF($C$2:$C$21,"*"&G2&"*",$D$2:$D$21) (Skansen is in cell G2)
How do i write the formula to return "Gross" and skansen together, lets say "Gross" is in cell G3?

 

[tyija1995]

I'd probably just do an addition in that case:

=SUMIF($C$2:$C$21,"*"&G2&"*",$D$2:$D$21)+SUMIF($C$2:$C$21,"*"&G3&"*",$D$2:$D$21)

 

[willwall]

Works great, thanks