Forum Discussion
Solved
IF Function with more criteria
I need to calculate based in two or more criteria and with multiple return values. For example: If base is open and type is narrow, then the result must be B4-1.75 If base is open and type is me...
yennyg If you have multiple criteria you will need to have more of a list format:
base type color value narrow open blue 1.75 narrow open red 2.25 narrow door blue 2.75 narrow door red 3.25 narrow closed blue 1 narrow closed red 3.75 medium open blue 6.5 medium open red 9.25 medium door blue 4.25 medium door red 2.5 medium closed blue 0.75 medium closed red -1 and then you can use a FILTER() function like:
=FILTER($D$8:$D$19,($A$8:$A$19=$F$8)*($B$8:$B$19=$G$8)*($C$8:$C$19=$H$8),"n/a")
see attached
mtarler Thank you so much.
How can I add another criteria?
I need to analyze three criteria.
Thanks a lot
yennyg If you have multiple criteria you will need to have more of a list format:
| base | type | color | value |
| narrow | open | blue | 1.75 |
| narrow | open | red | 2.25 |
| narrow | door | blue | 2.75 |
| narrow | door | red | 3.25 |
| narrow | closed | blue | 1 |
| narrow | closed | red | 3.75 |
| medium | open | blue | 6.5 |
| medium | open | red | 9.25 |
| medium | door | blue | 4.25 |
| medium | door | red | 2.5 |
| medium | closed | blue | 0.75 |
| medium | closed | red | -1 |
and then you can use a FILTER() function like:
=FILTER($D$8:$D$19,($A$8:$A$19=$F$8)*($B$8:$B$19=$G$8)*($C$8:$C$19=$H$8),"n/a")
see attached
- Thank you.