[Solved] Use wildcard (asterisk) with SUMIF

[iddqd]

I want to calculate the total values in column A via looking for a part of a string in column B

-SUM the A if the values in B contains FOOD
-SUM the A if the values in B contains the letter G
-SUM the A if the values in B contains the DAY

A       B
10	Monday Food
20	Tuesday Gym
30	Wednesday Gym
40	Thursday Shopping for groceries. also food.
50	Friday food
60	Saturday gym
70	Sunday groceries

for example. i tried =SUMIF(B1:B7, "*day*", A1:A7) but it doesn't work.

any help are greatly appreciated.

[FJCC]

The following works for me

Code: Select all

=SUMIF(B1:B7; ".*food.*"; A1:A7)

Note that the the wild card is .* and that the parts of the formula are separated by semicolons. OpenOffice uses regular expressions as wild cards and .* means "zero or more of any character". You may have to enable regular expression in formulas under OpenOffice -> Preferences -> OpenOffice Calc -> Calculate.

[iddqd]

The following works for me

Code: Select all

=SUMIF(B1:B7; ".*food.*"; A1:A7)

Note that the the wild card is .* and that the parts of the formula are separated by semicolons. OpenOffice uses regular expressions as wild cards and .* means "zero or more of any character". You may have to enable regular expression in formulas under OpenOffice -> Preferences -> OpenOffice Calc -> Calculate.

i could not find this use of wildcards in any documentation. thanks a lot, problem solved.

[FJCC]

The help lists it as "regular expressions;list of", so you have to know what a regular expression is to make the correct search. That's not as helpful as it could be.

[Alex1]

I would recommend to arrange the data in three columns and use a pivot table.

[iddqd]

I would recommend to arrange the data in three columns and use a pivot table.

loved the idea. will definitely use this to make my view clearer. thank you.

[iddqd]

The help lists it as "regular expressions;list of", so you have to know what a regular expression is to make the correct search. That's not as helpful as it could be.

thank you fjcc