COUNTIF help

[Ant]

I have a worksheet that records feedback from a training session. The
feedback can be ether strongly agree,, disagree or strongly disagree. I
have used a countif function to count how many of each type of response is
received and then based a chart on the results. This works fine however
using Auto filter I now need to filter the full list to so that I can
compare feedback between trainers or location or training units etc. When I
filter the list, my COUNTIF still counts hidden rows. My question is.... is
there a function like COUNTIF that will only count non filtered items or any
other suggestions on how to solve the problem.

Thanks

 

[Guest]

Have you thought about using a Pivot Table to summarize your results. It
would give you filtering options and much more.

 

[Bob Phillips]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="A
gree"))

 

[Aladin Akyurek]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="Agree")+0)

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="A
gree"))

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Ant]

Thanks for that! It works, I don't fully understand it but it is working
and that's the main thing.

I will now see if I can work it out!

Cheers

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="Agree")+0)

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="A
gree"))

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.