sumif only filtered area

[HSOLDO]

Hi,

i need to add only filtered area if criteria is met....

I am adding quantity of sales by salesperson.

B2 is salesperson name
H2 is the total quantity sold in filtered area. This is the cell i need calculated.

C13 to C1000 is salesperson name
D13 to D1000 is quantity sold for each sale.
E13 to E1000 is date

If i filter E13 to lets say March, I need H2 to show me total sum of sales A person in B2 made in filtered period.

Thx

 

[DanteAmor]

Try this

=SUMPRODUCT(SUBTOTAL(109,OFFSET($D$4,ROW($D$4:$D$1000)-ROW($D$4),,1))*($C$4:$C$1000=$B$2))

 

[kweaver]

Use the SUBTOTAL function:

=SUBTOTAL(109,D13:D1000)

This will sum all the values in that D column range that are visible after you apply your filter(s).

 

[MrsFraser07]

Try this

=SUMPRODUCT(SUBTOTAL(109,OFFSET($D$4,ROW($D$4:$D$1000)-ROW($D$4),,1))*($C$4:$C$1000=$B$2))

Thanks! This works for me too! I've been trying to do a subtotal with conditions and this works! I am wondering if you wouldn't mind explaining it though. I'm not sure what the OFFSET or anything really after the SUBTOTAL(109, portion of your formula does. Really appreciate it. I like to learn and understand so I can apply it to other worksheets.

 

[DanteAmor]

What it does offset, is to take the values ​​of D4 down, but only of the rows that are visible. (it is an internal process that performs excel, the values ​​in hidden cells return them as 0)
That way it only adds the "visible" data.

*($C$4:$C$1000=$B$2)
This means that C is equal to the content of B2

 

[MrsFraser07]

What it does offset, is to take the values ​​of D4 down, but only of the rows that are visible. (it is an internal process that performs excel, the values ​​in hidden cells return them as 0)
That way it only adds the "visible" data.

*($C$4:$C$1000=$B$2)
This means that C is equal to the content of B2

Awesome!!! Thanks. Love learning new tricks.

 

[DanteAmor]

Youre welcome & thanks for the feedback.