SUMIFS function not filter values as i filter data from dropdown arrow

P

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
157
Office Version
  1. 2021
Platform
  1. Windows
Shares Search Filter 2.xlsx
ABCDEFGHIJKL
1APPLEAdvanced Search Bar - All Column Search
2
3
4
5
676Average289.7422020.50
7DateParticularQtyBuy/SellPriceTotalsParticularQtyTotalsAVG
806/08/2024 APPLE50BUY287.0014350.00 APPLE8154702.25675.34
906/08/2024 APPLE25BUY287.807195.00 0.00 
1006/08/2024 APPLE1SELL475.50475.50 0.00 
search_bar
Cell Formulas
RangeFormula
C6,F6C6=SUBTOTAL(9,C8:C1000)
E6E6=F6/C6
A8:F11A8=FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Particular]))),"")
I8I8=FILTER(UNIQUE($B$8:$B$1000),UNIQUE($B$8:$B$1000)<>0)
J8:J10J8=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I8)
K8:K10K8=SUMIFS($F$8:$F$1000,$B$8:$B$1000,I8)
L8:L10L8=IFERROR(K8/J8, "")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A8:F1000Celldoes not contain a blank value textNO


As you seen in above excel in yellow cell J8 and L8 values are not filter as i filter data. J8 value should be 76 and L8 value should be 289.74. Kindly guide how to correct this. Thanks
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
it would help if you would post the "my_data" table and other components of the calculations you have in question.
 
Upvote 0
Maybe
Excel Formula: 
=SUMIFS(INDEX($A$8#,,3),INDEX($A$8#,,2),I8)
 
Upvote 0
awoohaw said:
it would help if you would post the "my_data" table and other components of the calculations you have in question.
Click to expand...
Here below:
my_data =my_data!$A$2:$G$989
also guide me how can i send you whole workbook as attached...

Shares Search Filter APPLE.xlsx
ABCDEF
1DateParticularQtyBuy/SellPriceTotals
206/08/2024 MICROSOFT25BUY135.73392.5
306/08/2024 SAMSUNG5BUY643.53217.5
406/08/2024 NETFLIX11BUY3347.736824.7
506/08/2024 APPLE50BUY28714350
606/08/2024 MICROSOFT5SELL49899.9249499.5
706/08/2024 SAMSUNG1BUY5187.75187.7
806/08/2024 NETFLIX25BUY135.843396
906/08/2024 APPLE25BUY287.87195
1006/08/2024 MICROSOFT11BUY1610.6517717.15
1106/08/2024 SAMSUNG4BUY2651.610606.4
1206/08/2024 NETFLIX1BUY2650.752650.75
1306/08/2024 APPLE1SELL475.5475.5
1405/08/2024 MICROSOFT25BUY649.316232.5
1505/08/2024 SAMSUNG1BUY476.85476.85
1605/08/2024 NETFLIX25BUY800.320007.5
1704/08/2024 APPLE5BUY6536.3532681.75
source_data
Cell Formulas
RangeFormula
A2:A17A2=IF(raw_data!A2="","",DATEVALUE(TEXT(raw_data!A2,"dd/mm/yyyy")))
B2:B17B2=IF(raw_data!C2="","",REPLACE(raw_data!C2,1,3,""))
C2:E17C2=IF(raw_data!D2="", "",raw_data!D2)
F2:F17F2=IFERROR([@Qty]*[@Price],"")
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$G$989A2
 
Upvote 0
From what I can see your formulae are returning the correct. You are filtering the data for "Apple " & you have 4 rows with that in col B. Namely rows 5, 9, 13 & 17.
 
Upvote 0
Fluff said:
Maybe
Excel Formula: 
=SUMIFS(INDEX($A$8#,,3),INDEX($A$8#,,2),I8)
Click to expand...
yours this code also returning the correct, but when i select drop down filter date say 06/08/2024, then it did not show according the filter rows.... like for sum sometimes we use subtotal function when we filter data. Also please guide me how i can send you whole workbook for better understanding. thanks.. here is my non-filter data below:

Shares Search Filter APPLE.xlsx
ABCDEFGHIJKL
1Advanced Search Bar - All Column Search
2
3
4
5
6220Average1926.87423911.30
7DateParticularQtyBuy/SellPriceTotalsParticularQtyTotalsAVG
806/08/2024 MICROSOFT25BUY135.703392.50 MICROSOFT66286841.654346.09
906/08/2024 SAMSUNG5BUY643.503217.50 SAMSUNG1119488.451771.68
1006/08/2024 NETFLIX11BUY3347.7036824.70 NETFLIX6262878.951014.18
1106/08/2024 APPLE50BUY287.0014350.00 APPLE8154702.25675.34
1206/08/2024 MICROSOFT5SELL49899.90249499.50 0.00 
1306/08/2024 SAMSUNG1BUY5187.705187.70 0.00 
1406/08/2024 NETFLIX25BUY135.843396.00 0.00 
1506/08/2024 APPLE25BUY287.807195.00 0.00 
1606/08/2024 MICROSOFT11BUY1610.6517717.15 0.00 
1706/08/2024 SAMSUNG4BUY2651.6010606.40 0.00 
1806/08/2024 NETFLIX1BUY2650.752650.75 0.00 
1906/08/2024 APPLE1SELL475.50475.50 0.00 
2005/08/2024 MICROSOFT25BUY649.3016232.50 0.00 
2105/08/2024 SAMSUNG1BUY476.85476.85 0.00 
2205/08/2024 NETFLIX25BUY800.3020007.50 0.00 
2304/08/2024 APPLE5BUY6536.3532681.75 0.00 
search_bar
Cell Formulas
RangeFormula
C6,F6C6=SUBTOTAL(9,C8:C1000)
E6E6=F6/C6
A8:F23A8=FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Particular]))),"")
I8:I11I8=FILTER(UNIQUE($B$8:$B$1000),UNIQUE($B$8:$B$1000)<>0)
J8:J23J8=SUMIFS(INDEX($A$8#,,3),INDEX($A$8#,,2),I8)
K8:K23K8=SUMIFS($F$8:$F$1000,$B$8:$B$1000,I8)
L8:L23L8=IFERROR(K8/J8, "")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A8:F1000Celldoes not contain a blank value textNO
 
Upvote 0
The formula in col J looks to be returning the correct values to me, in what way do you think they're wrong.
 
Upvote 0
Fluff said:
The formula in col J looks to be returning the correct values to me, in what way do you think they're wrong.
Click to expand...
I agree this returning the correct values, but after filter data, total should be corresponding to filter visible rows columns only . kindly look my first #1 post Apple C6 value is 76 correct as compared to J8 value is 81 which is not correct if i apply date filter.
 
Upvote 0
I have already told why that is happening & you ignored what I said.
 
Upvote 0
Fluff said:
I have already told why that is happening & you ignored what I said.
Click to expand...
Sorry if i am missing something ? my English is also not as good ... actually i am using unique function in I8, so that all stocks values comes like qty, avg in one go for apple, mirosoft , etc. but problem arises when i filter data range, date wise in A column from DropDown , then their averages not according to corresponding visible data . how to correct this that ?
in below sheet i change filter all to just 06/08/2024 only, Microsoft total qty as on 06/08/2024 is actual 41 after filter , but J8 showing 66 as total same , whether applied filter data or not J8 is not changing..

Shares Search Filter APPLE.xlsx
ABCDEFGHIJKL
1Advanced Search Bar - All Column Search
2
3
4
5
6
7DateParticularQtyBuy/SellPriceTotalsParticularQtyTotalsAVG
806/08/2024 MICROSOFT25BUY135.703392.50 MICROSOFT66286841.654346.09
906/08/2024 SAMSUNG5BUY643.503217.50 SAMSUNG1119488.451771.68
1006/08/2024 NETFLIX11BUY3347.7036824.70 NETFLIX6262878.951014.18
1106/08/2024 APPLE50BUY287.0014350.00 APPLE8154702.25675.34
1206/08/2024 MICROSOFT5SELL49899.90249499.50 0.00 
1306/08/2024 SAMSUNG1BUY5187.705187.70 0.00 
1406/08/2024 NETFLIX25BUY135.843396.00 0.00 
1506/08/2024 APPLE25BUY287.807195.00 0.00 
1606/08/2024 MICROSOFT11BUY1610.6517717.15 0.00 
1706/08/2024 SAMSUNG4BUY2651.6010606.40 0.00 
1806/08/2024 NETFLIX1BUY2650.752650.75 0.00 
1906/08/2024 APPLE1SELL475.50475.50 0.00 
search_bar
Cell Formulas
RangeFormula
A8:F23A8=FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Particular]))),"")
I8:I11I8=FILTER(UNIQUE($B$8:$B$1000),UNIQUE($B$8:$B$1000)<>0)
J8:J19J8=SUMIFS(INDEX($A$8#,,3),INDEX($A$8#,,2),I8)
K8:K19K8=SUMIFS($F$8:$F$1000,$B$8:$B$1000,I8)
L8:L19L8=IFERROR(K8/J8, "")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A8:F1000Celldoes not contain a blank value textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
What will be formula on Search Box as i type data filter accordingly...
2 3
Replies
26
Views
616
pankajgrover
P
P
  • Solved
Not able to correct filter data date wise, Kindly correct my formula...
Replies
1
Views
180
Anthony47
A
P
  • Question Question
How to obtain average values on columns filter
Replies
3
Views
213
StephenCrump
S
P
  • Locked
  • Question Question
Below formula which works on excel 365 only, but also need to be work on excel 2021...
Replies
1
Views
170
Fluff
Fluff
Lacan
  • Question Question
How to filtered/extract names and calculate difference between values?
2
Replies
16
Views
250
PeteWright
PeteWright

Forum statistics

Threads
1,224,816
Messages
6,181,139
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top