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

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


--
Regards
Frank Kabel
Frankfurt, Germany


Dilip Mistry wrote:
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

Invoking the Longre idiom...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C$4:C$30,ROW(C$4:C$3 0)-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 Wrote:
Hi

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

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

Thanks in Advanc

--
Aladin Akyure
-----------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...nfo&userid=416
View this thread: http://www.excelforum.com/showthread.php?threadid=27810

Thank you.

Solved my problem.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!