Hi,
I need help once again with my worksheet shown below.
Basically, Cell A2 is where you identify whether you want NONE (sum all clicks and orders) and Contains only (sum clicks and orders for search terms which contain specific keywords only under the Contains column). Using the formula I have now, if cell A2 is set to "Contains only", and there are no keywords under the the Contains column, total clicks and orders will show a value of 0. Is there any to make it add all clicks and keywords instead?
I hope I explained this well enough. Any help will be appreciated. Thank you!
I need help once again with my worksheet shown below.
Basically, Cell A2 is where you identify whether you want NONE (sum all clicks and orders) and Contains only (sum clicks and orders for search terms which contain specific keywords only under the Contains column). Using the formula I have now, if cell A2 is set to "Contains only", and there are no keywords under the the Contains column, total clicks and orders will show a value of 0. Is there any to make it add all clicks and keywords instead?
I hope I explained this well enough. Any help will be appreciated. Thank you!
| sample1.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Type | Total Clicks | Total Orders | ACFS | |||
| 2 | None | 97 | 31 | 3.13 | |||
| 3 | |||||||
| 4 | Customer Search Term | Clicks | Orders | Contains | contains found? | ||
| 5 | lip liner | 24 | 7 | not found | |||
| 6 | eye liners for women | 6 | 3 | not found | |||
| 7 | maybelline | 39 | 3 | not found | |||
| 8 | lines under eye treatment | 2 | 2 | not found | |||
| 9 | maybelline primer | 5 | 2 | not found | |||
| 10 | almay makeup for underline puffiness and dark circles | 1 | 1 | not found | |||
| 11 | black gel liner | 1 | 1 | not found | |||
| 12 | bright gel white eyeliner | 1 | 1 | not found | |||
| 13 | concealer maybelline | 2 | 1 | not found | |||
| 14 | corrector de ojeras maybelline | 4 | 1 | not found | |||
| 15 | eye mask fine lines | 1 | 1 | not found | |||
| 16 | eye mask for lines under eyes | 1 | 1 | not found | |||
| 17 | eye patch lines | 1 | 1 | not found | |||
| 18 | eye patches for fine lines and tired eyes | 1 | 1 | not found | |||
| 19 | eye patches for puffiness and fine lines | 1 | 1 | not found | |||
| 20 | eye treatment fine lines | 1 | 1 | not found | |||
| 21 | eyebrow tint maybelline peel off | 1 | 1 | not found | |||
| 22 | hydro liner | 1 | 1 | not found | |||
| 23 | instant eraser maybelline | 4 | 1 | not found | |||
Sheet1 (2) | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:C2 | B2 | =IF($A$2="None",SUBTOTAL(109,B5:B99999),IF($A$2="Contains only",SUMIF($E$5:$E$99999,"=found",B5:B99999),IF($A$2="Does not contain only",SUMIF(#REF!,"=found",B5:B99999),SUMIF(#REF!,"=Valid",B5:B99999)))) |
| D2 | D2 | =B2/C2 |
| E5:E23 | E5 | =IF(A5="","",IF(SUMPRODUCT((D$5:D$9999<>"")*(ISNUMBER(SEARCH(D$5:D$9999,A5))))>0,"found","not found")) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A2 | List | None, Contains only |
