Using subtotal with Count if function

kahmad

New Member
Joined
Sep 18, 2002
Messages
30
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

kahmad

New Member
Joined
Sep 18, 2002
Messages
30
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.

Please assist if Possible
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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!
 

kahmad

New Member
Joined
Sep 18, 2002
Messages
30

ADVERTISEMENT

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
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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
 

kahmad

New Member
Joined
Sep 18, 2002
Messages
30

ADVERTISEMENT

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
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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".
 

kahmad

New Member
Joined
Sep 18, 2002
Messages
30
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
 

Forum statistics

Threads
1,144,359
Messages
5,723,893
Members
422,524
Latest member
wirkkarn

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top