Table Help.xlsm | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
2 | Working | Working | No Formula | ||||||||||||||||||
3 | CAR | LIGHT BLUE | 238 | 5 | CAR | LIGHT blue | 238 | 5 | CAR | LIGHT blue | 5 | 5 | |||||||||
4 | CAR | grey | 8 | 8 | CAR | grey | 8 | CAR | grey | 8 | |||||||||||
5 | CAR | white | 201 | 3 | CAR | white | 201 | 3 | 3 | CAR | BLUE | 201 | 3 | ||||||||
6 | car | black | 632 | 4 | car | black | 632 | 4 | 4 | car | black | 632 | 4 | ||||||||
7 | car | grey | 1 | 1 | car | grey | 1 | car | DARK BLUE | 1 | 1 | ||||||||||
8 | 9 | 7 | 6 | ||||||||||||||||||
9 | =IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ") | =IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"") | |||||||||||||||||||
10 | |||||||||||||||||||||
11 | Above is a short table with formula in green cell | Above is a short table were I would like to put a formula | Above is a short table were I would like to put a formula | ||||||||||||||||||
12 | 3 checks and 1 return | Column M is what I would like the formula to return | Column M is what I would like the formula to return | ||||||||||||||||||
13 | Check 1. does B3:B7 = car | if I3:I7 = "car" | if O3:O7 = "car" | ||||||||||||||||||
14 | Check 2. does C3:C7 not have word blue | Does J3:J7 not contain word "blue" | does P3:P7 have word blue | ||||||||||||||||||
15 | Check 3. does D3:D7 not have any text | Does K3:K7 have any text | Does Q3:Q7 not have any text | ||||||||||||||||||
16 | Retune. If all criteria is met return E3:E7 in F3:F7 | If all criteria is met return L3:L7 in M3:M7 | If all criteria is met return R3:R7 in S3:S7 | ||||||||||||||||||
17 | |||||||||||||||||||||
18 | The differences between the 2 formulas would be | I have tried combining the formula's from other table's | |||||||||||||||||||
19 | theses parts of the formula | to find the formula for the above table but I cant get it right | |||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3:F7 | F3 | =IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ") |
F8,S8,M8 | F8 | =SUM(F3:F7) |
M3:M7 | M3 | =IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"") |