# Using subtotal with Count if function

##### New Member
Hi,
Need Urgent Help, to a peculier problem.
In an Excel list example pasted here,

When i need to Calculate averages of coloumn 3 and 4 below based on coloumn 2, i use the following formula -
=(SUMIF(\$D\$9:\$D\$27,"CDS",E9:E27))/COUNTIF(\$D\$9:\$F\$27,"CDS")

"C" "D" "E" "F"
104 ABX 34 36
105 ABX 18 18
112 CDS 26 27
113 CDS 17 18
116 CDS 42 43
117 CDS 16 17
125 ABX 19 20

Using above formula i can display averages of both ABX and CDS in Two Rows.

Now the Problem arises when i want to use subtotals based on filters.
e.g to get an average of the whole coloum "E", i can easily type in
=Subtotal(1,D9:D27) and Get a result.
But as stated earlier i need to get individual averages for abx and CDS as well in two rows, when i custom filter lets say coloum "C" for range between 101 and 116.

Any help??
This message was edited by kahmad on 2002-09-19 07:40

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Dear Mark,
Thanks very Much for your Response.
But i am not sure it can be adapted to my Problem.

Please have a look at the extract from my sheet.
SUBTOTAL COUNTIF PROB.xls
ABCDEF
3
4ConsortiaAverages2325
5ABXAverages2426
6CDSAVERAGES2324
7
8NUMBERSERCMBOPSSARE
901-Jan-022ASRABX5660
1002-Feb-022ASXABX3436
1109-Feb-022AHGCDS1818
1210-Mar-022ASGCDS2627
1323-Mar-022ASRCDS1718
1428-Mar-022ASXCDS4243
1504-Apr-022AHGCDS1617
1606-Apr-022ASRABX1920
1719-May-022ASXABX1919
1824-May-022AHGABX1112
1914-Jun-022XDMCDS2124
2016-Jun-022XDSCDS1213
2118-Jun-022XDXCDS2628
2219-Jun-022XTACDS4041
2314-Jul-022ASRCDS15.816.1
2418-Jul-022XCACDS1818
2504-Aug-022XDLABX1013
2609-Aug-022ASXABX25.634.8
2720-Aug-022AHGABX20.018.0
Sheet1

Now I need to be able to custom Filter based on the Dates i.e e.g Between 01/jan and 24th may.
This should change the averages in the Top rows i.e for ABX and CDS, as this data will be picked up by another another sheet to produce graphs.

But i am not sure it can be adapted to my Problem.

I belive it can. Setup a model like the one I provide (with your original data), apply a filter to column C for values less than 115, and see the change in the "CDS" average. Give it a try!

Thanks Very Much Mark, For your Effort.
Unfortunately i can only see the formula in Cell G2. I cannot see the Formulas on cells H2, H3 .. or K2 & K3, when i click on them,
I have a small warning on the Left Bottom corner saying - Javascript (0) error or something similar.

Please be so king as to give the Formulas in text.

Thanks

_________________
This message was edited by kahmad on 2002-09-19 11:57

I'm not quite sure why my HTML postings are not working, but here are the formulas...

G2 | =SUBTOTAL(2,\$C\$2:C2) [ fill down to G8 ]
H2 | =G2>N(G1) [ fill down to H8 ]
K2 | {=AVERAGE(IF((\$D\$2:\$D\$8=\$J2)*\$H\$2:\$H\$8,\$E\$2:\$E\$8))}
K3 | {=AVERAGE(IF((\$D\$2:\$D\$8=\$J3)*\$H\$2:\$H\$8,\$E\$2:\$E\$8))}
This message was edited by Mark W. on 2002-09-19 12:32

Dear Mark,
Thanks once again.
I entered the formula exactly as ststed by you but unfortunately get different results, dont know why ?
Any way its past 2200 hrs here in Germany and i am half brain dead, still in the office.
Mark W.xls
ABCDEFGHIJK
1cdefgh
2104ABX34361TRUEABX24.45714
3105ABX18182TRUECDS0
4112CDS26273TRUE
5113CDS17184TRUE
6116CDS42435TRUE
7117CDS16176TRUE
8125ABX19207TRUE
Sheet1

Thanks to help once more

These are array formulas...

K2 | {=AVERAGE(IF((\$D\$2:\$D\$8=\$J2)*\$H\$2:\$H\$8,\$E\$2:\$E\$8))}
K3 | {=AVERAGE(IF((\$D\$2:\$D\$8=\$J3)*\$H\$2:\$H\$8,\$E\$2:\$E\$8))}

See the Excel Help topic for "About array formulas and how to enter them".

Dear Mark,

Thanks Very Much.
It Works.
You are a star.
I owe you a Beer if you are in Hamburg anytime.

Best Regards
Khawar

Replies
10
Views
1K
Replies
3
Views
174
Replies
3
Views
159
Replies
3
Views
96
Replies
3
Views
278

1,218,620
Messages
6,143,515
Members
450,492
Latest member
Rusbus1972

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