Sumproduct - visible cells
 

Hi,

I trying to to use sumproduct only on visible cells and having a hard
time. I read through many help sites, but can't seem to get it to
work. Here's what I'm trying to do to a much larger file...

A B
1 2 300
2 2 200 (hidden/filtered)
3 3 100

How do I set up a formula to get 2*300 + 3*100 = 900?

Thanks.

tinman

Sumproduct - visible cells
 

One way is to do the multiplication in another column, say column C. So you
get

A B C
1 2 300 600
2 2 200 400 (hidden/filtered)
3 3 100 100


Then, SUM all visible rows in column C by using SUBTOTAL.

=SUBTOTAL(109, C1:C3)



" wrote:

Hi,

I trying to to use sumproduct only on visible cells and having a hard
time. I read through many help sites, but can't seem to get it to
work. Here's what I'm trying to do to a much larger file...

A B
1 2 300
2 2 200 (hidden/filtered)
3 3 100

How do I set up a formula to get 2*300 + 3*100 = 900?

Thanks.

tinman

Sumproduct - visible cells
 

I have several pairs of columns that i need to do this to and was
hoping to not have to add several "total" columns.

tinman

On Feb 27, 3:30 pm, Vergel Adriano
wrote:
One way is to do the multiplication in another column, say column C. So you
get

A B C
1 2 300 600
2 2 200 400 (hidden/filtered)
3 3 100 100

Then, SUM all visible rows in column C by using SUBTOTAL.

=SUBTOTAL(109, C1:C3)

" wrote:
Hi,

I trying to to use sumproduct only on visible cells and having a hard
time. I read through many help sites, but can't seem to get it to
work. Here's what I'm trying to do to a much larger file...

A B
1 2 300
2 2 200 (hidden/filtered)
3 3 100

How do I set up a formula to get 2*300 + 3*100 = 900?

Thanks.

tinman

Sumproduct - visible cells
 

A second approach would be to implement this custom function:

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

--
Jay


"Vergel Adriano" wrote:

One way is to do the multiplication in another column, say column C. So you
get

A B C
1 2 300 600
2 2 200 400 (hidden/filtered)
3 3 100 100


Then, SUM all visible rows in column C by using SUBTOTAL.

=SUBTOTAL(109, C1:C3)



" wrote:

Hi,

I trying to to use sumproduct only on visible cells and having a hard
time. I read through many help sites, but can't seem to get it to
work. Here's what I'm trying to do to a much larger file...

A B
1 2 300
2 2 200 (hidden/filtered)
3 3 100

How do I set up a formula to get 2*300 + 3*100 = 900?

Thanks.

tinman

Sumproduct - visible cells
 

Hi tinman -

Here is an updated version. It simply removes the Abs function from one
line; it's more universal.

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, input2.Column - input1.Column)
vis_SumProduct = vis_SumProduct + Product
End If
Next 'cl
End Function

--
Jay


" wrote:

I have several pairs of columns that i need to do this to and was
hoping to not have to add several "total" columns.

tinman

On Feb 27, 3:30 pm, Vergel Adriano
wrote:
One way is to do the multiplication in another column, say column C. So you
get

A B C
1 2 300 600
2 2 200 400 (hidden/filtered)
3 3 100 100

Then, SUM all visible rows in column C by using SUBTOTAL.

=SUBTOTAL(109, C1:C3)

" wrote:
Hi,

I trying to to use sumproduct only on visible cells and having a hard
time. I read through many help sites, but can't seem to get it to
work. Here's what I'm trying to do to a much larger file...

A B
1 2 300
2 2 200 (hidden/filtered)
3 3 100

How do I set up a formula to get 2*300 + 3*100 = 900?

Thanks.

tinman