Find the max in one column when other columns contain certain values

[bobbjx7]

In the below example table, I have 4 columns of data, Label Name, Size, Amount and Location. I am trying to come up with a formula to get the maximum size of the Amount column (column C) when the Label Name (column A) and Size (column B) contain a certain value and the location (column D) contains a certain text string (similar to a like or wildcard function). For example, I want to find the maximum Amount (Column C) when Label Name (column A) = "Label 1" and Size (column B) = 1 and Location (column D) contains the text 'name1'. I have a partial formula that works for everything except the string in the location (column D).


It is: {=MAX(IF((A2:A8="Label 1")*(B2:B8=1),C2:C8))}
Note: the column headings are in row 1 and the data actually starts in row 2.


How can I add a condition to look for a the phrase 'name1' in the location (column D) column?

The table data is not showing up very well. The Label Name column contains text like 'Label 1', 'Label 2', 'Label 3', Size contains values like '0.5', '1', '2', '3', Amount contains values like '10', '20', '80', '18', etc. Location contains text like '\node 1\name1', '\node 1\name2', etc.

Column A Column B Column C Column D
Label Name Size Amount Location

<TABLE style="WIDTH: 265pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=353><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=101>Label 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>0.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>10 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=124>\node 1\name1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Label 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">\node 1\name2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Label 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>80 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">\node 1\name1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Label 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">\node 1\name1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Label 3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">\node 1\name3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Label 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>27</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">\node 1\name2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Label 3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">\node 1\name3</TD></TR></TBODY></TABLE>


Thank you in advance for any assistance you can provide.

 

[alvinwlh]

Hi,

can Try:

Array Formula:

{=MAX(IF(D2:D8="\node 1\name1",IF(B2:B8=0.5,IF(A2:A8="Label 1",C2:C8))))}

 

[bobbjx7]

Thanks Alvin for your response. However, I may not have stated my question well enough and also my example data may have been misleading. My question is not how to find the entire text that is contained in the Location column (column D). It is his how to find a partial phrase or text that is contained in the Location column. Meaning, I am not trying to find '\node 1\name1', I am trying to find just the rows that contain 'name1' in the Location column. So, I am trying to find some way to use a wildcard or something similar (i.e. D2:D8="*name1") to look for Locations that contain the phrase "name1".

Also, my example data for the Location column (column D) probably should have shown examples like: "\node A\name1", "\node B\name2", "\node C\name1", "\node A\name3", etc. Basically, the data in the column can be different for every row.

 

[alvinwlh]

Hi,

Try:

{=MAX(IF(NOT(ISERROR(FIND("name3",D2:D8))),IF(A2:A8="Label 2",IF(B2:B8=5,C2:C8))))}

 

[T. Valko]

Hi,

Try:

{=MAX(IF(NOT(ISERROR(FIND("name3",D2:D8))),IF(A2:A8="Label 2",IF(B2:B8=5,C2:C8))))}

You can replace the 2 functions NOT(ISERROR with the single function COUNT.

=MAX(IF(COUNT(FIND("name3",D2:D8)),IF(A2:A8="Label 2",IF(B2:B8=5,C2:C8))))

Note that FIND is case sensitive. It might be better to use SEARCH which is not case sensitive.

=MAX(IF(COUNT(SEARCH("name3",D2:D8)),IF(A2:A8="Label 2",IF(B2:B8=5,C2:C8))))

 

[bobbjx7]

Thanks Alvin and Biff for your responses. I ended up using Alvin's suggestion and it is working great for me. Unfortunately, Biff, your suggestion did not produce the correct results so I could not use your suggestion. If desired I can show you examples.

Thanks again.
Bob

 

[T. Valko]

Thanks Alvin and Biff for your responses. I ended up using Alvin's suggestion and it is working great for me. Unfortunately, Biff, your suggestion did not produce the correct results so I could not use your suggestion. If desired I can show you examples.

Thanks again.
Bob

Ok, I see what the problem is...

Book1
	A	B	C	D
2	Label 2	5	72	my name3
3	Label 2	5	68	name13
4	Label 2	5	12	noname
5	Label 2	5	12	name3 yes
6	Label 2	5	88	this name3
7	Label 2	5	13	xxx
8	Label 2	5	9	xxx
Sheet1

Replace COUNT with ISNUMBER.

=MAX(IF(ISNUMBER(SEARCH("name3",D2:D8)),IF(A2:A8="Label 2",IF(B2:B8=5,C2:C8))))

Result = 88

Don't forget to array enter!

 

[bobbjx7]

Thanks Biff. The ISNUMBER approach worked for me too.

Bob

 

[T. Valko]

Thanks Biff. The ISNUMBER approach worked for me too.

Bob

Good deal. Thanks for the feedback!