Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

On Mon, 25 May 2009 11:51:00 -0700, Niclas
wrote:

Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

You could work your filtering criteria into the SUMPRODUCT equation.
--ron

The excel sheet/tables contains data from a survey. So I basicly need to
filter in a lot of different ways, and will only be interested in the
sumproduct of the rows that remains after filtering. At this point the the
formular also includes the non-visible rows. I am not sure this answers your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

One workaround would be to introduce a third column with the formula in C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly need to
filter in a lot of different ways, and will only be interested in the
sumproduct of the rows that remains after filtering. At this point the the
formular also includes the non-visible rows. I am not sure this answers your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

I have simplified the problem when writing. My worksheet contains about 130
colums, where I need to do the same thing. And due to other column data, I
actually only have 4 unused colums, so the suggested workaround is not going
to work. I might then copy some of the data to other worksheets, but I rather
not at least not as long as there is still hope. Splitting the data will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula in C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly need to
filter in a lot of different ways, and will only be interested in the
sumproduct of the rows that remains after filtering. At this point the the
formular also includes the non-visible rows. I am not sure this answers your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

Here's an example...

A1:C1 = column headers

A1 = Region
B1 = Quantity
C1 = Price

A2:A20 = regions = North, East, South, West. Each appears multiple times
B2:C20 = numeric values

Assume you filter on column A = West

This will give you the sum total of Quantity * Price for West:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-ROW(A2),0,1)),B2:B20,C2:C20)

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
I have simplified the problem when writing. My worksheet contains about 130
colums, where I need to do the same thing. And due to other column data, I
actually only have 4 unused colums, so the suggested workaround is not
going
to work. I might then copy some of the data to other worksheets, but I
rather
not at least not as long as there is still hope. Splitting the data will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula in
C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly need
to
filter in a lot of different ways, and will only be interested in the
sumproduct of the rows that remains after filtering. At this point the
the
formular also includes the non-visible rows. I am not sure this answers
your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each
row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to
include all
rows rather than just those appearing after the filtering. I have
read
through a lot of the Q&A's on this page, but still can't get it to
work.

Thus, I would be grateful for your help.
Thanks,
Niclas

Maybe a look at the help index for SUBTOTAL will help.

or
Range("A1").Value = _
Application.Sum(Range("A1:A3").SpecialCells(xlCell TypeVisible))

or
Sub sumvis()
mysum = 0
On Error Resume Next
For Each c In [a1:a10]
If c.EntireRow.Hidden < True Then
mysum = mysum + c.Value
End If
Next
MsgBox mysum
End Sub

or
Function vis_SumProduct(input1 As Range, input2 As Range)
For Each cl In input1.Cells
If cl.EntireRow.Hidden = False Then
Product = cl * cl.Offset(0, Abs(input2.Column - input1.Column))
vis_SumProduct = vis_SumProduct + Product
End If
Next 'cl
End Function


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

Whatever condition(s) you set for your filter can more then likely be
included in a Sumproduct() formula that will work *without* filtering.
You therefore filter your data for display purposes and place the Sumproduct
formula in say the header row (always visible).

This will calculate the entire datalist, *but match* the display of the
filter.

For example:
Headers in Row1,
Customer in A2 to A25
Purchased Amount in B2 to B25
Price in C2 to C25

Filter on a particular Customer.
Enter Customer name in D1.
Enter this formula in E1:

=SUMPRODUCT((A2:A25=D1)*B2:B25*C2:C25)

This will always return the correct calculations whether or not the datalist
is filtered.

You could of course include *many* variables in the formula to match your
filtering criteria.

To evaluate if this is a viable suggestion, post back with examples of your
filtering criteria.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------.

"Niclas" wrote in message
...
I have simplified the problem when writing. My worksheet contains about
130
colums, where I need to do the same thing. And due to other column data, I
actually only have 4 unused colums, so the suggested workaround is not
going
to work. I might then copy some of the data to other worksheets, but I
rather
not at least not as long as there is still hope. Splitting the data will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula in
C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly need
to
filter in a lot of different ways, and will only be interested in the
sumproduct of the rows that remains after filtering. At this point the
the
formular also includes the non-visible rows. I am not sure this
answers your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each
row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to
include all
rows rather than just those appearing after the filtering. I have
read
through a lot of the Q&A's on this page, but still can't get it to
work.

Thus, I would be grateful for your help.
Thanks,
Niclas

Very creative. Hats off to you.

"T. Valko" wrote:

Here's an example...

A1:C1 = column headers

A1 = Region
B1 = Quantity
C1 = Price

A2:A20 = regions = North, East, South, West. Each appears multiple times
B2:C20 = numeric values

Assume you filter on column A = West

This will give you the sum total of Quantity * Price for West:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-ROW(A2),0,1)),B2:B20,C2:C20)

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
I have simplified the problem when writing. My worksheet contains about 130
colums, where I need to do the same thing. And due to other column data, I
actually only have 4 unused colums, so the suggested workaround is not
going
to work. I might then copy some of the data to other worksheets, but I
rather
not at least not as long as there is still hope. Splitting the data will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula in
C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly need
to
filter in a lot of different ways, and will only be interested in the
sumproduct of the rows that remains after filtering. At this point the
the
formular also includes the non-visible rows. I am not sure this answers
your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each
row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to
include all
rows rather than just those appearing after the filtering. I have
read
through a lot of the Q&A's on this page, but still can't get it to
work.

Thus, I would be grateful for your help.
Thanks,
Niclas

Thanks!

Let's see if that's what the OP had in mind.

--
Biff
Microsoft Excel MVP


"Sheeloo" wrote in message
...
Very creative. Hats off to you.

"T. Valko" wrote:

Here's an example...

A1:C1 = column headers

A1 = Region
B1 = Quantity
C1 = Price

A2:A20 = regions = North, East, South, West. Each appears multiple times
B2:C20 = numeric values

Assume you filter on column A = West

This will give you the sum total of Quantity * Price for West:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-ROW(A2),0,1)),B2:B20,C2:C20)

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
I have simplified the problem when writing. My worksheet contains about
130
colums, where I need to do the same thing. And due to other column
data, I
actually only have 4 unused colums, so the suggested workaround is not
going
to work. I might then copy some of the data to other worksheets, but I
rather
not at least not as long as there is still hope. Splitting the data
will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula
in
C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly
need
to
filter in a lot of different ways, and will only be interested in
the
sumproduct of the rows that remains after filtering. At this point
the
the
formular also includes the non-visible rows. I am not sure this
answers
your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after
each
row is
first multiplied (a2*b2 ...a100*b100). This appears to work
fine.

However, once I filter the data, the SUMPRODUCT continues to
include all
rows rather than just those appearing after the filtering. I
have
read
through a lot of the Q&A's on this page, but still can't get it
to
work.

Thus, I would be grateful for your help.
Thanks,
Niclas

I think this is what OP wanted... of course expanded to include all columns
- "need to filter in a lot of different ways, and will only be interested in
the sumproduct of the rows that remains after filtering"

"T. Valko" wrote:

Thanks!

Let's see if that's what the OP had in mind.

--
Biff
Microsoft Excel MVP


"Sheeloo" wrote in message
...
Very creative. Hats off to you.

"T. Valko" wrote:

Here's an example...

A1:C1 = column headers

A1 = Region
B1 = Quantity
C1 = Price

A2:A20 = regions = North, East, South, West. Each appears multiple times
B2:C20 = numeric values

Assume you filter on column A = West

This will give you the sum total of Quantity * Price for West:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-ROW(A2),0,1)),B2:B20,C2:C20)

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
I have simplified the problem when writing. My worksheet contains about
130
colums, where I need to do the same thing. And due to other column
data, I
actually only have 4 unused colums, so the suggested workaround is not
going
to work. I might then copy some of the data to other worksheets, but I
rather
not at least not as long as there is still hope. Splitting the data
will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula
in
C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly
need
to
filter in a lot of different ways, and will only be interested in
the
sumproduct of the rows that remains after filtering. At this point
the
the
formular also includes the non-visible rows. I am not sure this
answers
your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after
each
row is
first multiplied (a2*b2 ...a100*b100). This appears to work
fine.

However, once I filter the data, the SUMPRODUCT continues to
include all
rows rather than just those appearing after the filtering. I
have
read
through a lot of the Q&A's on this page, but still can't get it
to
work.

Thus, I would be grateful for your help.
Thanks,
Niclas

I have spend the morning (Europe time) checking this and it is exactly what I
needed. Thank you very much.

Niclas

"T. Valko" skrev:

Here's an example...

A1:C1 = column headers

A1 = Region
B1 = Quantity
C1 = Price

A2:A20 = regions = North, East, South, West. Each appears multiple times
B2:C20 = numeric values

Assume you filter on column A = West

This will give you the sum total of Quantity * Price for West:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-ROW(A2),0,1)),B2:B20,C2:C20)

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
I have simplified the problem when writing. My worksheet contains about 130
colums, where I need to do the same thing. And due to other column data, I
actually only have 4 unused colums, so the suggested workaround is not
going
to work. I might then copy some of the data to other worksheets, but I
rather
not at least not as long as there is still hope. Splitting the data will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula in
C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly need
to
filter in a lot of different ways, and will only be interested in the
sumproduct of the rows that remains after filtering. At this point the
the
formular also includes the non-visible rows. I am not sure this answers
your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each
row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to
include all
rows rather than just those appearing after the filtering. I have
read
through a lot of the Q&A's on this page, but still can't get it to
work.

Thus, I would be grateful for your help.
Thanks,
Niclas

Thanks for this. I have no doubt that one of these will do the job, but I am
afraid that this programming extend beyond my capabilities. But thank you
anyway.

Niclas

"Don Guillett" skrev:

Maybe a look at the help index for SUBTOTAL will help.

or
Range("A1").Value = _
Application.Sum(Range("A1:A3").SpecialCells(xlCell TypeVisible))

or
Sub sumvis()
mysum = 0
On Error Resume Next
For Each c In [a1:a10]
If c.EntireRow.Hidden < True Then
mysum = mysum + c.Value
End If
Next
MsgBox mysum
End Sub

or
Function vis_SumProduct(input1 As Range, input2 As Range)
For Each cl In input1.Cells
If cl.EntireRow.Hidden = False Then
Product = cl * cl.Offset(0, Abs(input2.Column - input1.Column))
vis_SumProduct = vis_SumProduct + Product
End If
Next 'cl
End Function


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after each row is
first multiplied (a2*b2 ...a100*b100). This appears to work fine.

However, once I filter the data, the SUMPRODUCT continues to include all
rows rather than just those appearing after the filtering. I have read
through a lot of the Q&A's on this page, but still can't get it to work.

Thus, I would be grateful for your help.
Thanks,
Niclas

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
I have spend the morning (Europe time) checking this and it is exactly what
I
needed. Thank you very much.

Niclas

"T. Valko" skrev:

Here's an example...

A1:C1 = column headers

A1 = Region
B1 = Quantity
C1 = Price

A2:A20 = regions = North, East, South, West. Each appears multiple times
B2:C20 = numeric values

Assume you filter on column A = West

This will give you the sum total of Quantity * Price for West:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-ROW(A2),0,1)),B2:B20,C2:C20)

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
I have simplified the problem when writing. My worksheet contains about
130
colums, where I need to do the same thing. And due to other column
data, I
actually only have 4 unused colums, so the suggested workaround is not
going
to work. I might then copy some of the data to other worksheets, but I
rather
not at least not as long as there is still hope. Splitting the data
will
provide other problems for some of the statistical analyses.

Niclas

"Sheeloo" skrev:

One workaround would be to introduce a third column with the formula
in
C2;
=A2*B2

then use SUBTOTAL(9, your_range) function on Col C


"Niclas" wrote:

The excel sheet/tables contains data from a survey. So I basicly
need
to
filter in a lot of different ways, and will only be interested in
the
sumproduct of the rows that remains after filtering. At this point
the
the
formular also includes the non-visible rows. I am not sure this
answers
your
question, but hopefully it does.

Niclas

"T. Valko" skrev:

OK, so what is the condition that you want the sum of?

--
Biff
Microsoft Excel MVP


"Niclas" wrote in message
...
Hi,

I have two tables a2:a100 and b2:b100. I am using the formular
=SUMPRODUKT(A2:A100;B2:B100) to arrive at a total sum, after
each
row is
first multiplied (a2*b2 ...a100*b100). This appears to work
fine.

However, once I filter the data, the SUMPRODUCT continues to
include all
rows rather than just those appearing after the filtering. I
have
read
through a lot of the Q&A's on this page, but still can't get it
to
work.

Thus, I would be grateful for your help.
Thanks,
Niclas