Sumproduct A Filtered Column

**Steve-B** · 07-03-2008, 06:25 AM

Hi all,

The following function allows a count of days as breakdowns (column AL). If I want to change the first array so that instead of only MARCH entires being counted to entires filtered on column AK4:AK20000 what would be the correct sytax to use?

Many thanks....

=SUMPRODUCT(--($AK4:$AK20000="MARCH"),--($T4:$T20000="Provide Home Treatment"),--($AL4:$AL20000<=3))

**daddylonglegs** · 07-03-2008, 07:06 AM

Hello Steve,

Try this [edited.....twice for accuracy]

=SUMPRODUCT(SUBTOTAL(3,OFFSET($T4,ROW($T4:$T20000)-ROW($T4),0)),($T4:$T20000="Provide Home Treatment")*($AL4:$AL20000<=3))

Will only count visible rows where your two conditions apply

**reggie1000** · 07-03-2008, 07:14 AM

hi steve-b,

i dont know of anyway to sumproduct a filtered column, but you can use a drop down with the unique cells in it, you can also add extra dropdowns for the other varibles in the sumproduct.

heres an example

thansk reg