Can I use COUNTIF in subtotals?

[S Bond]

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]

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]

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