Sum Cells In a Range IF 3 'Contains' Criteria are Met

[darstar]

Hi all, hoping you can help me. I want to sum cells within a range, only if 3 criteria are met, but each criteria is specific to finding specific text within other cells in the same row. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Set 1[/TD]
[TD="align: center"]Set 2[/TD]
[TD="align: center"]Count[/TD]
[/TR]
[TR]
[TD]Good fruit[/TD]
[TD]This apple is red[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD]Bad fruit[/TD]
[TD]This apple is green[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD]Bad dessert[/TD]
[TD]This cake is red[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD]Good meat[/TD]
[TD]This beef is red[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]Bad fruit[/TD]
[TD]This apple is red[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

I would like to create a formula that sums the numbers in column 'Count' if the following criteria are met for column 'Set 1' and 'Set 2':

'Set 1' - Cell contains the word "fruit"
'Set 2' - Cell contains the word "apple" AND "red"

In this example, the formula should return "6."

Apologies, I know the example looks a bit weird.

Would really appreciate your help! Thanks in advance.

 

[RoryA]

Welcome to the forum.

You can use SUMIFS for that:

=SUMIFS(C:C,A:A,"*fruit*",B:B,"*apple*",B:B,"*red*")

 

[darstar]

Welcome to the forum.

You can use SUMIFS for that:

=SUMIFS(C:C,A:A,"*fruit*",B:B,"*apple*",B:B,"*red*")

---------

Thank you for both the welcome, as well as the reply, Rory!

If I may ask another question, what does the use of the "*" do in this context? Sorry, I've never seen asterisks used in a formula before.

 

[RoryA]

It's a wildcard meaning any number of characters (including none). So "*apple*" looks for apple anywhere in the text, whereas "apple*" would look for text starting with apple, and "*apple" would look for text ending with apple. (all 3 would match just apple). You can also use a ? as a wildcard to indicate 1 character.

 

[darstar]

Wonderful, thank you! One follow-up question, is there any way to use a cell value to represent the text within the "**"?

For example, rather than typing "*apple*", if the word "apple" is in cell A2, I would, ideally, like to use the cell value in place of the text in the formula (i.e. "*A2*").

This does not seem to work, which is why I'm here Any potential workarounds?

 

[RoryA]

You can use:

"*"&A2&"*"

as the criterion.