subtotal of cells containing specific data

[reynastus]

Hi all

I have a table that is autofiltered and would like to do a counta on a cell range of the filtered data based on the contents of that cell.

the table is like this

A B C D
----------------------------------------------------------
Date - Data 1 - Data 2 - Data 3
----------------------------------------------------------
Date1 - Name1 - type1 - Stuff 1
Date2 - Name2 - Type2 - Stuff 3
Date3 - Name3 - Type1 - Stuff 2
Date4 - Name4 - Type1 - Stuff 3

Theres alot more entries than this but thats the general idea of the table

I put the filter on for a month and lets say that dates 2-4 remain I have one formula that counts these being

Please Login or Register  to view this content.

Is there a way that I could perform a subtotal counta on all the entries in column D (the stuff column) that would show up the number of "Stuff 3" counts?

I was thinking

Please Login or Register  to view this content.

But this doesnt work any hints?

Cheers
Rey

[reynastus]

Thought a spreadsheet example might help to explain what I was trying to do as my post might not have been that clear

Rey

[ConneXionLost]

I'd suggest using COUNTIF like this:

Please Login or Register  to view this content.

but it will ignore the autofilter and continue to count the entire column.

[reynastus]

cheers for the reply but the whole point was to count the entries once filter was in.

any other ideas?

[mahju]

Hi
You can use this formula and then filter on column H for that word i,e "word1"

Please Login or Register  to view this content.

Regards

[DonkeyOte]

I put the filter on for a month and lets say that dates 2-4 remain...

Is there a way that I could perform a subtotal counta on all the entries in column D (the stuff column) that would show up the number of "Stuff 3" counts?

In general terms, conducting conditional calcs with Filtered data

Please Login or Register  to view this content.

Note use of 8:100 - modify as appropriate but do not make execessive as the above is inefficient and Volatile.

If you're only interested ever in Col H you could use:

Please Login or Register  to view this content.

Then your summary is

Please Login or Register  to view this content.

[reynastus]

Thanks for the help I will try if i get the chance this afternoon

@Mahju
The reason I wanted it without the second filter was so that I could see how many of "stuff 1" was in month "blah" and how much of "Word2" was in there at a glance without having to count entries or filter a 2nd time. This spreadsheet is not for me so needs to be fairly easy to use.

@DonkeyOte
I've used a formula similar to the first one (=sumproduct) and understand the whole volatile thing in large datasets. yes it works but its tempramental.

I have never seen this before

Please Login or Register  to view this content.

I'll google it and see if that is what I am looking for I'm guessing it would be but I dont know what the formula does / means.

Thanks for the assistance either way

[reynastus]

@ DonkeyOte

Thanks that helped heaps

Still not sure why it has to be done that way but for what ever reason it is working. I tried to also use that formula (the short one) to do a countif for a number greater than another number [countif(K:K,">10")] but this did not read the numbers as figured from the =rept formula if that makes sense. I used the =sumproduct formula you used and modified it to suite my needs as well and that works perfectly as well and my co-worker is very happy with the results.

Cheers
Rey

[DonkeyOte]

I tried to also use that formula (the short one) to do a countif for a number greater than another number [countif(K:K,">10")] but this did not read the numbers as figured from the =rept formula if that makes sense.

REPT is a string based function (ie numbers would be converted to strings)

Where the criteria column for your COUNTIF are to be numeric use an appropriate SUBTOTAL function on each line, eg:

Please Login or Register  to view this content.

Then use the standard COUNTIF against column K.

In XL2007 and above we'd generally use a single helper column to generate a 1/0 visibility flag (based on SUBTOTAL with COUNTA on each row) and then use COUNTIFS - specifying that visible column must = 1.

Prior to XL2007 you need to consider alternatives... you could apply the same single visibility column and use SUMPRODUCT rather than COUNTIFs but it would still be less efficient (IMO) than using specific helper columns with standard COUNTIF etc...