Subtotal function with Criteria

[Dilip Mistry]

Hi

Has anyone created a macro that uses the functionality of the SUBTOTAL with
the functionality of the SUMIF.

I am trying to sum only visible cells using a selection criteria.

Thanks in Advance

 

[Frank Kabel]

Hi
try the following:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$10,1,1),ROW($A$1:$A$10)-
ROW(INDEX($A$1:$A$10,1,1)),0))=1),--($A$1:$A$10="lookup_value"),$B$1:$B
$10)

looks for the value 'lookup_value' in column A and sums the
corresponding value in column B

 

[Aladin Akyurek]

Invoking the Longre idiom...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C$4:C$30,ROW(C$4:C$30)-MIN(ROW(C$4:C$30)),,1)),--($B$4:$B$30=$A2))

where C4:C30 is the range to sum and B4:B10 the range for which A2 (th
condition) must hold.

Note. 9 instead of 3 in the SUBTOTAL(...) bit is due (in a post a
MrExcel) to Domenic.

 

[Dilip Mistry]

Thank you.

Solved my problem.