How to sumproduct only filtered data

[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:

 

[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:

 

[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(xlCellTypeVisible))

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
(e-mail address removed)

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

 

[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(xlCellTypeVisible))

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
(e-mail address removed)

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

You're welcome. Thanks for the feedback!