COUNTIF help
 

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

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

--
Regards,
Ron


"Ant" wrote:

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

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


--
HTH

Bob Phillips

"Ant" wrote in message
...
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

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

Bob Phillips wrote:
=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.

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

"Aladin Akyurek" wrote in message
...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="Agree")+0)

Bob Phillips wrote:
=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.