Modify SUMIF and COUNTIF to work with SUBTOTALS
 

I use SUMIF and COUNTIF formula's to sum and count data within specific
ranges. Here are the formula's I use to find data from 5.0 to 15.0:

=SUM(N2:N220,-SUMIF(N2:N220,{"<=5.0","=15.0"}))

=COUNTIF(N2:N220,"=5.0")-COUNTIF(N2:N220,"=15.0")

Is there a way to modify these formula's to work with SUBTOTALS?

Thanks,

Steve

Hi
try something like
=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=5),--($A$1:$A$10<=15))

--
Regards
Frank Kabel
Frankfurt, Germany

"SSHO_99" schrieb im Newsbeitrag
...
I use SUMIF and COUNTIF formula's to sum and count data within
specific
ranges. Here are the formula's I use to find data from 5.0 to 15.0:

=SUM(N2:N220,-SUMIF(N2:N220,{"<=5.0","=15.0"}))

=COUNTIF(N2:N220,"=5.0")-COUNTIF(N2:N220,"=15.0")

Is there a way to modify these formula's to work with SUBTOTALS?

Thanks,

Steve

If you are wanting to combine the formulas you use with AutoFilter,
you'll need the Longre idiom for visible cells...

1.

=SUBTOTAL(9,N2:N220)-SUMPRODUCT(SUBTOTAL(3,OFFSET(N2:N220,ROW(N2:N220)-MIN(ROW(N2:N220)),,1)),--(N2:N220=5),--(N2:N220<=15))

2.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(N2:N220,ROW(N2:N220)-MIN(ROW(N2:N220)),,1)),--(N2:N220=5),--(N2:N220<=15))

SSHO_99 Wrote:
I use SUMIF and COUNTIF formula's to sum and count data within specific
ranges. Here are the formula's I use to find data from 5.0 to 15.0:

=SUM(N2:N220,-SUMIF(N2:N220,{"<=5.0","=15.0"}))

=COUNTIF(N2:N220,"=5.0")-COUNTIF(N2:N220,"=15.0")

Is there a way to modify these formula's to work with SUBTOTALS?

Thanks,

Steve


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=277905