Sum multiple columns and using wildcards

[mb1074]

Would like to sum multiple columns for all the rows which includes certain characters, for example in the attached spreadsheet I want to total column Jan and Feb for all fruit which which ends with "berry",

[Richard Buttrey]

Hi

=SUMIFS(B3:B7,A3:A7,"*berry")

change for B3:B7 for other months

[mb1074]

Thanks Richard, but I would need the total for both months together.

[protonLeah]

=SUMPRODUCT(--ISNUMBER(SEARCH("*berry",A3:A7))*B3:C7)

[mb1074]

Thanks Ben, it works, but what if I have 3 months, Jan, Feb & Mar and I only want to calculate the *berry for the months of Jan and Mar, excluding Feb?

[JohnTopley]

assuming Mar is in column D

=SUM(SUMPRODUCT(--ISNUMBER(SEARCH("*berry",A3:A7))*((B2:D2="Jan") +(B2:D2="Mar")) *B3:D7))

[Richard Buttrey]

....why not put your data into a format that can be used by a Pivot Table driven by a Slicer or two. You'll then have a far more flexible analysis tool and won't need any formulae

[wk9128]

Formula