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

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

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

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