Subtotal if

[mikebecker]

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.

 

[Zack Barresse]

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?

 

[mikebecker]

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.

 

[mikebecker]

GW 11-10.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1											RoutesDispatched	0
2											AssetsDispatched	1157
3											AssetsServiced	1038
4											MissedAssets	119
5											ASAD-AccountsServicedAsDispatched	90%
6
7											AverageSellDown%	50%
8											AverageDropSize	6.6
9	LocID	COF	IdentItemID	AssetNo	DispDate		RT	Cap	Units	SellDown%
10	190	2858661	4058594	5669515	11/10/2005	190124	124	640	414	65%	Below.325
11	190	5221987	4339000	5628666	11/10/2005	190124	124	320	210	66%	Between.325and.65
12	190	5221987	4339011	7858425	11/10/2005	190124	124	360	210	58%	Above.65
13	190	5221987	4339017	5774461	11/10/2005	190124	124	300	120	40%
14	190	5235009	8515597	5650446	11/10/2005	190124	124	280	180	64%
15	190	2858661	8841316	7854740	11/10/2005	190124	124
GW 11-10

 

[PaddyD]

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.

 

[Zack Barresse]

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.

 

[mikebecker]

Perfect!