pankajgrover
Board Regular
- Joined
- Oct 27, 2022
- Messages
- 157
- Office Version
- 2021
- Platform
- Windows
Already (Filter Ctrl+Shift+L) option is on my header. there are 3 dates 04 Aug, 05 Aug, 06 Aug.. when i select all date or only 06/08/2024 right side unique stocks average comes , but when i filter on date and select 04 Aug or 05 Aug dates unique stocks average data not visible , here screen shot attached.. kindly look where i am mistaken .. Thanks
I am not able to correct filter data date wise, here is below also xl2bb sheet..
here is the link for complete workbook also for download My Workbook
I am not able to correct filter data date wise, here is below also xl2bb sheet..
here is the link for complete workbook also for download My Workbook
| Shares Search Filter APPLE.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Advanced Search Bar - All Column Search | |||||||||||||
| 2 | ||||||||||||||
| 3 | ||||||||||||||
| 4 | ||||||||||||||
| 5 | Unique Stocks Average | |||||||||||||
| 6 | 189 | Average | 2013.07 | 380470.60 | ||||||||||
| 7 | Date | Particular | Qty | Buy/Sell | Price | Totals | Particular | Qty | Totals | AVG | ||||
| 8 | 06/08/2024 | MICROSOFT | 25 | BUY | 135.70 | 3392.50 | 1 | MICROSOFT | 66 | 286841.65 | 4346.09 | |||
| 9 | 06/08/2024 | SAMSUNG | 5 | BUY | 643.50 | 3217.50 | 1 | SAMSUNG | 10 | 14300.75 | 1430.08 | |||
| 10 | 06/08/2024 | NETFLIX | 11 | BUY | 3347.70 | 36824.70 | 1 | NETFLIX | 36 | 56832.20 | 1578.67 | |||
| 11 | 06/08/2024 | APPLE | 50 | BUY | 287.00 | 14350.00 | 1 | APPLE | 77 | 22496.00 | 292.16 | |||
| 12 | 06/08/2024 | MICROSOFT | 5 | SELL | 49899.90 | 249499.50 | 1 | 0.00 | ||||||
| 13 | 06/08/2024 | APPLE | 25 | BUY | 287.80 | 7195.00 | 1 | 0.00 | ||||||
| 14 | 06/08/2024 | MICROSOFT | 11 | BUY | 1610.65 | 17717.15 | 1 | 0.00 | ||||||
| 15 | 06/08/2024 | SAMSUNG | 4 | BUY | 2651.60 | 10606.40 | 1 | 0.00 | ||||||
| 16 | 05/08/2024 | APPLE | 1 | SELL | 475.50 | 475.50 | 1 | 0.00 | ||||||
| 17 | 05/08/2024 | MICROSOFT | 25 | BUY | 649.30 | 16232.50 | 1 | 0.00 | ||||||
| 18 | 05/08/2024 | SAMSUNG | 1 | BUY | 476.85 | 476.85 | 1 | 0.00 | ||||||
| 19 | 04/08/2024 | NETFLIX | 25 | BUY | 800.30 | 20007.50 | 1 | 0.00 | ||||||
| 20 | 04/08/2024 | APPLE | 1 | SELL | 475.50 | 475.50 | 1 | 0.00 | ||||||
search_bar | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C6,F6 | C6 | =SUBTOTAL(9,C8:C1000) |
| E6 | E6 | =F6/C6 |
| A8:F20 | A8 | =FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Particular]))),"") |
| G8:G20 | G8 | =MAP(INDEX(A8#,,1),LAMBDA(m,SUBTOTAL(103,m))) |
| I8:I11 | I8 | =FILTER(UNIQUE($B$8:$B$1000),UNIQUE($B$8:$B$1000)<>0) |
| J8 | J8 | =SUMIFS($C$8:$C$1000,$B$8:$B$1000,I8,$G$8:$G$1000,1) |
| K8 | K8 | =SUMIFS($F$8:$F$1000,$B$8:$B$1000,I8,$G$8:$G$1000,1) |
| L8:L20 | L8 | =IFERROR(K8/J8, "") |
| J9:J20 | J9 | =SUMIFS($C$8:$C$1000,$B$8:$B$1000,I9) |
| K9:K20 | K9 | =SUMIFS($F$8:$F$1000,$B$8:$B$1000,I9) |
| Dynamic array formulas. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A8:F1000 | Cell | does not contain a blank value | text | NO |
