Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
summing visible cells using SUMIF/SUMPRODUCT not SUBTOTAL | Excel Discussion (Misc queries) | |||
sumproduct and visible cells | Excel Worksheet Functions | |||
SUMPRODUCT TO CALCULATE VISIBLE CELLS ONLY | Excel Worksheet Functions | |||
Help: Copying Visible Cells only to Visible cells! | Excel Programming |