HELP AVERAGEIFS: Partially Match Text

[Xiangwu]

Hi, All.

It's my first post, but I do need help. I need to average the numbers that one criteria does not exactly match the title. See the example.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Domestic API5L/X42[/TD]
[TD]$5.4[/TD]
[/TR]
[TR]
[TD]Imported API5L/X42[/TD]
[TD]$4.7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ERW API5L/X42, FBE[/TD]
[TD]$3.8[/TD]
[/TR]
[TR]
[TD]
Average API5L/X42[/TD]
[TD]=averageifs()[/TD]
[/TR]
</tbody>[/TABLE]

Need to get this work done. So, Waiting for the answer online.

Thank you guys, so much!

Xiangwu

 

[barry houdini]

Helo Xiangwu, welcome to MrExcel

Try using AVERAGEIFS with wildcards like this:

=AVERAGEIF(A:A,"*API5L/X42*",B:B)

 

[Xiangwu]

Thank you for your welcoming, Barry.

I have some other tables in the spreadsheet, so I can not use the wildcards. Also, *API5L/X42* means the text is in the middle of the title, but actually, API5L/X42 may be in the middle, at the end or in the front. I tried and it does not work well. Wondering whether you have any other ideas.

Helo Xiangwu, welcome to MrExcel

Try using AVERAGEIFS with wildcards like this:

=AVERAGEIF(A:A,"*API5L/X42*",B:B)

 

[barry houdini]

I have some other tables in the spreadsheet, so I can not use the wildcards.

Why not? Do you mean you need other conditions too? If so you can use AVERAGEIFS to introduce further conditions

*API5L/X42* means the text is in the middle of the title, but actually, API5L/X42 may be in the middle, at the end or in the front

The * wildcard denotes any number of characters, including zero, so using *API5L/X42* will include any row with the value API5L/X42, whether at the start, end or middle....or even if it's the entire cell contents

 

[Xiangwu]

Ok, I see what you mean. That's smart. I was trapped with other tables after mentioned the wildcards.

Why not? Do you mean you need other conditions too? If so you can use AVERAGEIFS to introduce further conditions



The * wildcard denotes any number of characters, including zero, so using *API5L/X42* will include any row with the value API5L/X42, whether at the start, end or middle....or even if it's the entire cell contents