MEDIAN IF Contains Matching Text

frankendoc

New Member
Joined
Apr 2, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
HI,
I'm trying to do the Median equivalent of AVERAGEIF(A1:A10,"*"&C12"&*",B1:B10) where Column C has text some of which is contained within a longer string in Column A and Column B has numerical values that I want to find the median of.
I found other threads where this was possible with the wildcards if the search term was typed in but not if the search term is in another cell (C12 in this case).
Any help would be greatly appreciated.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Welcome to Mr. Excel.

It would help if you show a few examples and use XL2BB to do that.
 
Upvote 0
Hi there, something like:
Excel Formula:
{=MEDIAN(IF(ISNUMBER(FIND(C12,A1:A10)),B1:B10))}
and enter with CTRL+SHIFT+ENTER
 
Upvote 0
Hi there, something like:
Excel Formula:
{=MEDIAN(IF(ISNUMBER(FIND(C12,A1:A10)),B1:B10))}
and enter with CTRL+SHIFT+ENTER
Not sure whether this is actually relevant to the OPs data as we don't know much about that, but to be truly equivalent to the AVERAGEIF formula posted, that formula would need to use SEARCH instead of FIND.

Just an aside too: If the OP is using this in their Excel 365 as opposed to Excel 2016, CTRL+SHIFT+ENTER confirmation would not be required.

.. and another 365, but not 2016, option would be

Excel Formula:
=MEDIAN(FILTER(B1:B10,ISNUMBER(SEARCH(C12,A1:A10))))
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top