Modify SUMIF and COUNTIF to work with SUBTOTALS

G

Guest

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
 
F

Frank Kabel

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))
 
A

Aladin Akyurek

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top