Sumproduct visible cells

**Even** · 03-25-2013, 10:05 AM

Hi, I am trying to sum visible cells within a specified range. I have been searching the Internet, but can't find what I am looking for. I want to sum the premium that is in a specified time period. The time periods are July 1st to June 30th every year. So within these periods I want to sum the premium for the each product that is chosen (I am using filter). The formula I have sums everything, but what I need is to sum only the visible cells. I hope somebody can help me.

Here is the formula: =SUMPRODUCT(--(INT($A$2:$A$1360)>=E8);--(INT($A$2:$A$1360)<=F8);--($A$2:$A$1360))

**daddylonglegs** · 03-25-2013, 10:20 AM

I had to convert your dates in column A but I think that's a regional thing - assuming valid dates try this formula in G3 copied down to sum only visible premiums within the date period

=SUMPRODUCT((A$2:A$1360>=E3)*(A$2:A$1360<=F3),SUBTOTAL(9,OFFSET(B2,ROW(B$2:B$1360)-ROW(B$2),0)))

**Even** · 03-25-2013, 04:06 PM

I was thinking, while I am on the subject, if it is possible to sum the premium for each product like in the columns I, J and K in the attached example. Might be a complete different thread though.

**ChemistB** · 03-25-2013, 11:33 AM

I did something similar to DDL but used a dummy column (D) This column can be hidden after setup.

D17 (I moved everything down so your table doesn't disappear when you filter) copied down contains
=SUBTOTAL(3,C17)
This formula will equal 0 when filtered.

Then the formula is something like
=SUMPRODUCT(--(INT($A$8:$A$1366)>=F2),--(INT($A$8:$A$1366)<=G2),--($B$8:$B$1366), --($D$8:$D$1366>0))

**Even** · 03-25-2013, 12:19 PM

Fantastic - thanks daddylonglegs and ChemistB!!!

**daddylonglegs** · 03-25-2013, 04:13 PM

Do you want to sum all cells or still only visible ones? If it's the former you can use SUMIFS like thisin I2 copied across and down:

=SUMIFS($B:$B,$A:$A,">="&$F2,$A:$A,"<="&$G2,$C:$C,I$1)