SUMPRODUCT with filter

[Greg Snidow]

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

 

[Joel]

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]

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

 

[Greg Snidow]

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

 

[Mike H.]

Not sure I understand your situation either, but perhaps, you could create an
advanced filter with the criteria for the filter also being used in the
sumproduct? Then you could see the rows and see the sumproduct results both
based on the same criteria.

 

[Joel]

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]

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)*(D512 = "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