Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT with filter
Greetings all. I am trying to use SUMPRODUCT with a filter, but I can not
find any posts that answer my questions. I have a spreadsheet with around 10,000 rows, with column A being "JobNum", and each Job number accounts for around 200 of the rows on the spreadsheet. I have the following: =SUMPRODUCT(($I$13:$I$65000)*($M$13:$M$65000 = "Cable")*($H$13:$H$65000 = "CC")) in a cell at the top of the sheet, where column "I" is a numerical footage, column "M" is a material type, and column H is a line item status. I would like to be able to filter for each job number in column A, and show the above SUMPRODUCT, but only for the rows displayed in my filter. I tried adding the SUBTOTAL function to the above as follows, but it is displaying a value far greater than it should. =SUMPRODUCT((SUBTOTAL(9,$I$13:$I$65000))*($M$13:$M $65000 = "Cable")*($H$13:$H$65000 = "CC")). Any ideas? Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT with filter
I don't know why you need subtotal. Sumproduct will do the summing. if you
have 200 rows your answer is going to be 200 times the correct value. I like using the -- instead of the *. Try this =SUMPRODUCT(($I$13:$I$65000),--($M$13:$M$65000 = "Cable"),--($H$13:$H$65000 = "CC")) "Greg Snidow" wrote: Greetings all. I am trying to use SUMPRODUCT with a filter, but I can not find any posts that answer my questions. I have a spreadsheet with around 10,000 rows, with column A being "JobNum", and each Job number accounts for around 200 of the rows on the spreadsheet. I have the following: =SUMPRODUCT(($I$13:$I$65000)*($M$13:$M$65000 = "Cable")*($H$13:$H$65000 = "CC")) in a cell at the top of the sheet, where column "I" is a numerical footage, column "M" is a material type, and column H is a line item status. I would like to be able to filter for each job number in column A, and show the above SUMPRODUCT, but only for the rows displayed in my filter. I tried adding the SUBTOTAL function to the above as follows, but it is displaying a value far greater than it should. =SUMPRODUCT((SUBTOTAL(9,$I$13:$I$65000))*($M$13:$M $65000 = "Cable")*($H$13:$H$65000 = "CC")). Any ideas? Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT with filter
Thanks Joel. That still did not work. The reason I tried SUBTOTAL is that I
have one field with my formula at the top of my spreadsheet. I need to be able to filter column "A" for job 1 say, and it will calculate a number based on two criteria. If I filter for job 2 say, I then want it to only show the results for job 2. If I use SUBTOTAL only, there is no problem, as it only displays the total of values within my filter. The problem is that I need to subtotal *AND* I need to sumproduct. Does that make sense? Greg "Joel" wrote: I don't know why you need subtotal. Sumproduct will do the summing. if you have 200 rows your answer is going to be 200 times the correct value. I like using the -- instead of the *. Try this =SUMPRODUCT(($I$13:$I$65000),--($M$13:$M$65000 = "Cable"),--($H$13:$H$65000 = "CC")) "Greg Snidow" wrote: Greetings all. I am trying to use SUMPRODUCT with a filter, but I can not find any posts that answer my questions. I have a spreadsheet with around 10,000 rows, with column A being "JobNum", and each Job number accounts for around 200 of the rows on the spreadsheet. I have the following: =SUMPRODUCT(($I$13:$I$65000)*($M$13:$M$65000 = "Cable")*($H$13:$H$65000 = "CC")) in a cell at the top of the sheet, where column "I" is a numerical footage, column "M" is a material type, and column H is a line item status. I would like to be able to filter for each job number in column A, and show the above SUMPRODUCT, but only for the rows displayed in my filter. I tried adding the SUBTOTAL function to the above as follows, but it is displaying a value far greater than it should. =SUMPRODUCT((SUBTOTAL(9,$I$13:$I$65000))*($M$13:$M $65000 = "Cable")*($H$13:$H$65000 = "CC")). Any ideas? Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT with filter
Maybe this will help me explain
=SUMPRODUCT((A13:A65000 = "6A01213")*(I13:I65000)*($M$13:$M$65000 = "Cable")*($H$13:$H$65000 = "CC")) In the above example, I have simply added Column A, JobNum to the SUMPROCUCT formula, and it displays *exactly* the output I need in the one cell where I have the formula typed. Now, lets say I want to look at the value where JobNum = '6A01214', I can either type that value into the formula above, or figure out some way to make the value come from the filter. Any clearer? Greg "Joel" wrote: I don't know why you need subtotal. Sumproduct will do the summing. if you have 200 rows your answer is going to be 200 times the correct value. I like using the -- instead of the *. Try this =SUMPRODUCT(($I$13:$I$65000),--($M$13:$M$65000 = "Cable"),--($H$13:$H$65000 = "CC")) "Greg Snidow" wrote: Greetings all. I am trying to use SUMPRODUCT with a filter, but I can not find any posts that answer my questions. I have a spreadsheet with around 10,000 rows, with column A being "JobNum", and each Job number accounts for around 200 of the rows on the spreadsheet. I have the following: =SUMPRODUCT(($I$13:$I$65000)*($M$13:$M$65000 = "Cable")*($H$13:$H$65000 = "CC")) in a cell at the top of the sheet, where column "I" is a numerical footage, column "M" is a material type, and column H is a line item status. I would like to be able to filter for each job number in column A, and show the above SUMPRODUCT, but only for the rows displayed in my filter. I tried adding the SUBTOTAL function to the above as follows, but it is displaying a value far greater than it should. =SUMPRODUCT((SUBTOTAL(9,$I$13:$I$65000))*($M$13:$M $65000 = "Cable")*($H$13:$H$65000 = "CC")). Any ideas? Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT with filter
Wow, thanks Joel, I think. I'm not sure what some of that stuff is, so it
will take me probably a couple of days to investigate everything with which I am not familiar, but I will give it a shot. But in the mean time, here is a visual of my problem, with =SUMPRODUCT((C5:C12)*(D5:D12 = "Cable")*(E5:E12 = "CC")) populated in C3. If I put a filter on Column A, and filter for Job1, the answer should be 25, and if I filter for Job2, the answer should be 20. With SUMPRODUCT, the answer is always the same, no matter what filter I put on, it looks at the entire sheet. Is there a way to reference the value chosen in the filter list box? If so it would seem like I could add another array to my SUMPRODUCT function that says *(A5:A12 = "list value"). I do realize, that by filtering the entire row 4, I could easily just use SUBTOTAL, and filter for "Cable" and "CC". However, this is a very simple example, and in reality its just a pain in the rear end to keep changing 10 different filters. Thanks for the help, and as I said, it will probabl be a few. I now I've rambled again, sorry. A B C D 1 2 CC Cable 45 3 4 JOB FTG TYPE STATUS 5 Job1 100 Duct RC 6 Job1 75 Duct CC 7 Job1 50 Cable RC 8 Job1 25 Cable CC 9 Job2 200 Duct RC 10 Job2 100 Duct CC 11 Job2 50 Cable RC 12 Job2 20 Cable CC "Joel" wrote: I don't really understand exactly what you are trying to do. Anyway here are some answers 1) Look up subtotal in worksheet help. Look at "Insert Sbutotals". 2) Use a Pivot Table with subtotals. 3) Use a menu data - Filter - autofilter. Insert a blank row 1 where the filter will be put. then for your sumproduct formula use the filter address in Row so you can select the Job Number You want filters. 4) You can put multiple Job numbers in a column and the add to the sumproduct Vlookup to only add the items in multiple job number column. 5) Add all the job number to a listbox. Use the LinkedCell Property of the Listbox to put the selected item on the worksheet. Then use this cell in you sumproduct formula. To get unique Job Numbers use menu Data - Filter - Advance Filter and select the unique box. Then copy the unique values to a new column. You can link the List box to these values using the Listbox property ListFillRange. Because the datqa in a Listbox is a string you need to use VALUE() to convert the string to a number. "Greg Snidow" wrote: Maybe this will help me explain =SUMPRODUCT((A13:A65000 = "6A01213")*(I13:I65000)*($M$13:$M$65000 = "Cable")*($H$13:$H$65000 = "CC")) In the above example, I have simply added Column A, JobNum to the SUMPROCUCT formula, and it displays *exactly* the output I need in the one cell where I have the formula typed. Now, lets say I want to look at the value where JobNum = '6A01214', I can either type that value into the formula above, or figure out some way to make the value come from the filter. Any clearer? Greg "Joel" wrote: I don't know why you need subtotal. Sumproduct will do the summing. if you have 200 rows your answer is going to be 200 times the correct value. I like using the -- instead of the *. Try this =SUMPRODUCT(($I$13:$I$65000),--($M$13:$M$65000 = "Cable"),--($H$13:$H$65000 = "CC")) "Greg Snidow" wrote: Greetings all. I am trying to use SUMPRODUCT with a filter, but I can not find any posts that answer my questions. I have a spreadsheet with around 10,000 rows, with column A being "JobNum", and each Job number accounts for around 200 of the rows on the spreadsheet. I have the following: =SUMPRODUCT(($I$13:$I$65000)*($M$13:$M$65000 = "Cable")*($H$13:$H$65000 = "CC")) in a cell at the top of the sheet, where column "I" is a numerical footage, column "M" is a material type, and column H is a line item status. I would like to be able to filter for each job number in column A, and show the above SUMPRODUCT, but only for the rows displayed in my filter. I tried adding the SUBTOTAL function to the above as follows, but it is displaying a value far greater than it should. =SUMPRODUCT((SUBTOTAL(9,$I$13:$I$65000))*($M$13:$M $65000 = "Cable")*($H$13:$H$65000 = "CC")). Any ideas? Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT help with filter | Excel Discussion (Misc queries) | |||
Sumproduct for YTD with text filter | Excel Discussion (Misc queries) | |||
Filter or sumproduct (Question for Gord Dibben) | Excel Discussion (Misc queries) | |||
Filter and SumProduct | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |