CountIF Formula including invisible cells

[rjferrani22]

Hi,

I'm trying to analyze data from a survey where we assign a point value to each response and then average the scores for each demographic. I'm running into a problem when I start to filter the data based on location/role of the user. I'm using a countif formula to count the number of responses that meet a certain criteria, but when I filter to only view the North for example, the formula is still including responses from the other regions. Is there a way to make excel only count the visible cells? Or should I use a different formula?

Here's the formula I'm using if this will help: =(COUNTIF('Data with Point Values'!N$7:N$34,$C$3))*(VLOOKUP($C$3,'Data with Point Values'!$J$1:$K$3,2,FALSE))

Thanks in advance!

 

[Jonmo1]

Which version of Excel are you using?

If XL2007+ try adding a helper column say column O
IN O7 and filled down to O34 put
=SUBTOTAL(103,N7)

Then use
=COUNTIFS('Data with Point Values'!N$7:N$34,$C$3,'Data with Point Values'!O$7:O$34,1)*VLOOKUP($C$3,'Data with Point Values'!$J$1:$K$3,2,FALSE)