Can I use COUNTIF in subtotals?

S Bond · Mar 27th, 2002, 09:02 AM

Can I change COUNT to COUNTIF in creating subtotals? I have existign subtotals but want to further summarise based on a specific conditon.

all help appreciated.

IML · Mar 27th, 2002, 09:14 AM

Not pretty but you could highlight your row and

1) find =subtotal(3,
replace with zzzcountif(

2) find )
replace with ,your criteria)

3) find zzz
replace with =

Chris Davison · Mar 27th, 2002, 04:08 PM

Good evening Mr Bond,

are you using the =SUBTOTAL function because some of your rows become filtered out ?

I was after a similar function that subtotalled (on filtered rows) but with conditions a little while ago.

Guess what ? I failed.

in the end, I stuck my criteria in columns over to thr right

ie : if a1="dog" then put in the value
next column if a1="cat" then put in the value

etc etc etc

then, underneath these columns (which are your criteria) you apply the same =subtotal function as your original ranges.... it effectively subtotals each column which is in itself based on a *single* criteria

if you have 2 or more, just use an =AND function

not much help eh ?

Chris

edit.......

if your subtotals are just =SUMS try a search in this board on "SUMPRODUCT" and "Aladin" he has some great sumproduct formulae that sum and count based on single or multiple criteria....

[ This Message was edited by: Chris Davison on 2002-03-27 15:13 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 09:02 AM	#1
S Bond New Member Join Date: Mar 2002 Posts: 1	Can I change COUNT to COUNTIF in creating subtotals? I have existign subtotals but want to further summarise based on a specific conditon. all help appreciated.

Mar 27th, 2002, 09:14 AM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	Not pretty but you could highlight your row and 1) find =subtotal(3, replace with zzzcountif( 2) find ) replace with ,your criteria) 3) find zzz replace with =

Mar 27th, 2002, 04:08 PM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Good evening Mr Bond, are you using the =SUBTOTAL function because some of your rows become filtered out ? I was after a similar function that subtotalled (on filtered rows) but with conditions a little while ago. Guess what ? I failed. in the end, I stuck my criteria in columns over to thr right ie : if a1="dog" then put in the value next column if a1="cat" then put in the value etc etc etc then, underneath these columns (which are your criteria) you apply the same =subtotal function as your original ranges.... it effectively subtotals each column which is in itself based on a single criteria if you have 2 or more, just use an =AND function not much help eh ? Chris edit....... if your subtotals are just =SUMS try a search in this board on "SUMPRODUCT" and "Aladin" he has some great sumproduct formulae that sum and count based on single or multiple criteria.... [ This Message was edited by: Chris Davison on 2002-03-27 15:13 ]