Hello, I am hoping someone can help me with a formula issue. Thank you for your help in advance.
Column A: Items
Column B: Type of the item is
Column C: Value of the occurrence
I am attempting to have a value returned which will havemultiple criteria and return the maximum value for that corresponding value.
For example, I need to find an item in column A which isalso type 1, and find the maximum value of that occurrence.
The items in column A and the types in column B will haveduplicate values, and the two columns are not dependent on each other.
So far using an array I have figured out how to achieve thiswith one criterion, but the second criteria is giving me issues. My array isbasically a MAX function with an IF function nested in it.
{=MAX(IF(A:A=$H2,C:C]))
In the spreadsheet I'm making, the data shown on Columns G through J would be on a separate tab.
<tbody>
</tbody>
Column A: Items
Column B: Type of the item is
Column C: Value of the occurrence
I am attempting to have a value returned which will havemultiple criteria and return the maximum value for that corresponding value.
For example, I need to find an item in column A which isalso type 1, and find the maximum value of that occurrence.
The items in column A and the types in column B will haveduplicate values, and the two columns are not dependent on each other.
So far using an array I have figured out how to achieve thiswith one criterion, but the second criteria is giving me issues. My array isbasically a MAX function with an IF function nested in it.
{=MAX(IF(A:A=$H2,C:C]))
In the spreadsheet I'm making, the data shown on Columns G through J would be on a separate tab.
A | B | C | D | E | F | G | H | I | J | |
1 | Item | Type | Value | Type 1 | Type 2 | Type 3 | ||||
2 | Item 1 | Type 1 | 2 | Item 1 | MAX | MAX | MAX | |||
3 | Item 1 | Type 1 | 18 | Item 2 | MAX | MAX | MAX | |||
4 | Item 1 | Type 2 | 1 | Item 3 | MAX | MAX | MAX | |||
5 | Item 1 | Type 3 | 6 | Item 4 | MAX | MAX | MAX | |||
6 | Item 1 | Type 3 | 16 | |||||||
7 | Item 2 | Type 3 | 7 | |||||||
8 | Item 2 | Type 1 | 11 | |||||||
9 | Item 2 | Type 2 | 14 | |||||||
10 | Item 3 | Type 2 | 11 | |||||||
11 | Item 4 | Type 2 | 17 | |||||||
12 | Item 4 | Type 2 | 17 | |||||||
13 | Item 4 | Type 1 | 11 | |||||||
14 | Item 4 | Type 1 | 14 | |||||||
15 | Item 4 | Type 1 | 1 |
<tbody>
</tbody>