# Subtotal if

#### mikebecker

##### Board Regular
I have column J that contains percentages ranging from 0% to 100%

I also have a filtered column A of locations.

I want to be able to filter by lcolumn A and count from column J and change each time I choose a different location.

1. the number of occurances <32%
2. the number of occurances =>32% and =<65%
3. the number of occurances >65%

I've used subtotal formulas but run into errors when I try to combine with countif.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hello mikebecker,

Can you post exactly what criterion you will be using here? Can you explain, "..I've used subtotal formulas but run into errors when I try to combine with countif." Perhaps some examples of what you've tried?

This is what I've tried.

=subtotal(2,countif(J10:J1166,"<.325"))

I'm trying to count the number of cells that are < .325 in Column J when I filter column A. There are many different values "locations" in column A.

GW 11-10.xls
ABCDEFGHIJKL
1RoutesDispatched0
2AssetsDispatched1157
3AssetsServiced1038
4MissedAssets119
6
7AverageSellDown%50%
8AverageDropSize6.6
9LocIDCOFIdentItemIDAssetNoDispDateRTCapUnitsSellDown%
1019028586614058594566951511/10/200519012412464041465%Below.325
1119052219874339000562866611/10/200519012412432021066%Between.325and.65
1219052219874339011785842511/10/200519012412436021058%Above.65
1319052219874339017577446111/10/200519012412430012040%
1419052350098515597565044611/10/200519012412428018064%
1519028586618841316785474011/10/2005190124124
GW 11-10

MIke,

Re-read the html maker FAQ thread at the top of the board to see how to address the nbsp issue, then re-post your snapshot. Also, usually best to provide some commentary when you post up a sheet image.

Try something like this ...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A40,ROW(A2:A40)-ROW(A2),0,1)),--(J2:J40<0.325))

.. where your data extends to row 40. Change the ragnes to suit.

Perfect!

Replies
9
Views
283
Replies
6
Views
69
Replies
2
Views
464
Replies
1
Views
369
Replies
2
Views
80

1,196,048
Messages
6,013,095
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back