Filters and 'CountIf' formula

[Killian12321]

Hi all,

I've got a little problem that I've been unable to solve. Have been browsing the web all morning but not been able to find anything to help; thought I'd find a excel help site and post my query here

Anyway; I've been given an Excel sheet that I've been asked to do a little work on. The sheet looks a little like this;

WBRI    N    1    1    1    1 
WBRI    N    2    2    1    3 
WBRI    N    3    3    3    2 
WBRI    N    2    1    2    1 
WBRI    N    1    2    1    1  

The first column represents ethnicity and the rest are rating to questions (each column is another question rated from 1 - 4). They've asked me to do a formula that will tell me how many people voted 1, 2, 3 & 4 individually in each column alongside what the average vote was.

Now thats pretty easy but the issue comes when they start using their autofilter based on ethnicity. I have managed to stop the formula from disappearing by putting it outside of the 'autofilter' though the formula still represents the whole data rather than the filtered data.

Can anyone suggest how to get around this? There are several sheets each with different questions (more or less columns) and different amounts of people answering (more or less rows) and the above is course only a small section of one sheet just to provide an example.

Can anyone help?

Thanks for reading it whether you can or not though

[ncmay]

Have you tried the SUBTOTAL function?

[DonkeyOte]

To do conditional counts with filtered data you're generally looking at embedding the SUBTOTAL function within SUMPRODUCT (SUBTOTAL in it's own right does not do conditional tests like COUNTIF) -- the downside to this is that you end up with Volatile Arrays (sumproducts are processed like an array and so can be "hungry" performance wise - making them volatile is therefore pretty bad news - lots of them and your file will grind to a halt)

Might be an idea to post a quick sample file with some dummy data outlining expected results when various filters are put in place - there may be workarounds.

[Killian12321]

I did try =subtotal(9,range) but it didn't give me any values. Wouldn't be any good for me anyway as I need to know how many people voted 1, 2, 3 & 4 individually so am assuming I'm going to have to use 4 seperate countif statements.

[Killian12321]

Ill prepare a little dummy file now and upload

[Killian12321]

Right; I've uploaded a dummy file with fake data in to show what I mean.

The data reads fine but as soon as you filter to by an ethnicity such as 'BASI' the 'countif' statements and the corresponding data vanish.

Any ideas?

[DonkeyOte]

Here is a slightly different approach - based off your file.

You must move your results table away from your filtering area or the results will be hidden etc pending selections.

To get the Average you can take the SUM of the #X columns and divide by SUM of B:E on results tab.

[Killian12321]

That seems perfect DonkeyOte. Will try implementing into the actual sheet now.

Would you mind exactly whats happening with the =K2*SUBTOTAL(3,K2)? Am assuming it's K2x1 but just trying to get my head around how that changes the results and stops them disappearing.

Thanks again for your help though.

Really appreciate it.

[DonkeyOte]

Would you mind exactly whats happening with the =K2*SUBTOTAL(3,K2)?

The SUBTOTAL function as we know will only work with visible rows. Function 3 in SUBTOTAL is COUNTA therefore SUBTOTAL(3,K2) will return 1 if K2 is neither blank nor hidden - if either condition were not met (ie K2 either blank or non-blank but hidden) the result would be 0.

You can then in turn use this result (1/0) and multiply the initial score by it thereby ensuring all hidden rows are automatically assigned a result of 0 and only visible rows maintain their initial value.

Note:

I would have used COUNT rather than COUNTA (2 rather than 3 in the SUBTOTAL) but your numbers are in fact stored as text so COUNT would fail.

Post XL2003 you can use 103 rather than 3 if you need to

Do read through the XL Help Files on SUBTOTAL as they are actually pretty good.


The approach suggested does use a lot of Volatiles so is not brilliant but I would say it's still likely to perform better than using multiple Volatile SUMPRODUCT / SUBTOTAL formulas.

[Killian12321]

Excellent. Makes sense now

Appreciate all the help you've given. Made my life much easier.

[Killian12321]

Typical

Have just got round to attempting it on my actual sheet and although everything is identical it still doesn't seem to be working. I'm thinking that I may be missing something stupid but for the life of me can't figure out what.

Would you mind checking out the attached dummy2 file? It's pretty much an exact replica of one of the sheets I'm working on only with details removed where necessary.

Think I may get work to pay for a good advanced Excel Course as I've always considered myself a high intermediate user though it doesn't seem to be the case

[DonkeyOte]

Your COUNTIF function is referencing the wrong row for the criteria value ie should be B$2 as opposed to B$1

[Killian12321]

Ah; so the blank line at the top of my 'information' sheet is to blame

Just tried it in my master sheet and it's worked a charm.

Thanks so much yet again.

[yupie]

I have 2 tabs:
The first is for the filling data only and I use a filter.
The second tab is only for summaries, when I use a function SUBTOTAL.

The problem is I have to change SUBTOTAL every day. Is there any SUBTOTAL condition function?

(if D2:D20 contains "LX300", use subtotal for H2:H20) -look at the picture

Does anyone a solution?
Thx

[DonkeyOte]

yupie, welcome to the board though please note:

Your post does not comply with Rule 2 of our Forum RULES.
Don't post a question in the thread of another member -- start your own thread.
If you feel it's particularly relevant, provide a link to the other thread.

On a final note - if you want help with an Excel file then it's generally a good idea to post an Excel file as opposed to an image.