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.

Domestic API5L/X42	$5.4
Imported API5L/X42	$4.7
ERW API5L/X42, FBE	$3.8
Average API5L/X42	=averageifs()

<tbody>
</tbody>

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