Combine subtotal and countif functions in excel 2

[DrSmyth]

Hi,

I've got a bit of a tricky one on excel. I have a table with several colums of data that has an autofilter.

Above one of the columns is a countif function. I would like to somehow combine this with a subtotal function so that the countif figure is representative of any filtered data instead of just giving me the calculation for the full unfiltered spreadsheet.

Any ideas???

 

[KenWright]

I think you would probably need to use something like SUMPRODUCT and feed it the same parameters as the Autofilter.

Regards
Ken

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DrSmyth]

Hi Kenwright, just had a quick look at the sumproduct function on ms help and don't see how i can apply this to my problem....

What's your thinking here, I've not used sumproduct function before so may be missing something integral.

Dr

 

[xlbo]

sumproduct acts like an array formula. Lets say you have data filtered on Col A = "001" and col C = "PB"

to get a count of the unique items (in Col B) that fulfill that criteria - ie those that are filtered, you could use

=SUMPRODUCT((A2:A1000="001")*(C2:C1000="PB"))

to get a number of rows filtered (given unique identifiers per row)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

Give us your data ranges, what kind of data it is, and the parameters you have used on those ranges to filter your data, and then tell us what it is you want to count, and we'll have a go at building the formula for you.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DrSmyth]

Ok, the main colums concerned are a, which has a range of A8:A3000 and c with the same row count but values of "Yes" or "No".

I want to be able to filter by column A (This can be done by any one of 27 variables) and for the formula relating to column C to count how many "Yes" row's there are.

 

[xlbo]

=sumproduct((A8:A3000="Filtered Variable")*(C8:C3000="Y"))

should do the trick


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[DrSmyth]

Cheers Geoff, is it possible to make the "Filtered Variable" part of the question dynamic, or will we have to change this when we filter by a different value?

 

[KenWright]

Throw a cell reference in in place of the hardcoded element, and then whatever goes in the cell reference becomes your filter.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DrSmyth]

Cheers for the help lads, just got back to work after a bit of time off (Just turned 30 so thought i'd spend a few days fishing)....

Your suggestion works like a dream stars all round

 

[xlbo]

Well happy birthday Doc


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[DrSmyth]

Cheers Geoff....

 

[KenWright]

Good stuff - Now we just have to get you into Pivot tables as well <g>

and Congrats on the birthday!!

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------