Function '=Frequency' Help!

[James8309]

Hi everyone,

I am experiencing a problem with my frequency formulae. if anyone
could help that would be great!


Basically, in sheet1 I have this massive data file (row up to
380,000). column B:B has 12 different types, let's just say Type A to
Type L.


When I apply a autofilter on the first row to look at Type A only
because I am only interested in Type A.


And when I try to do a frequency on sheet2 on that Type A auto-
filtered data.
i.e. ' = frequency(DATA, Sheet2!A2:A10)'
instead of performing this formulae on what's filtered, it does for
the whole 380,000 rows.


How do I make it perform frequency function on what's filtered only?


thank you in advance


regards,


James

 

[Jarek Kujawa]

if you need the number of "Type A" occurences then why not try:

=COUNTIF(A1:A380000,"Type A")

if you need to arrive at a sum of A1:A380000 when there "Type A" in
column B, try:

=SUM(IF(B1:B380000="Type A",A1:A380000,))
CTRL+SHIFT+ENTER it instead of simply using ENTER

 

[James8309]

if you need the number of "Type A" occurences then why not try:

=COUNTIF(A1:A380000,"Type A")

if you need to arrive at a sum of A1:A380000 when there "Type A" in
column B, try:

=SUM(IF(B1:B380000="Type A",A1:A380000,))
CTRL+SHIFT+ENTER it instead of simply using ENTER

I am trying to get a frequency count where Bin array is from
Range("A2:A10")

Thanks for your help

 

[Spiky]

I am trying to get a frequency count where Bin array is from
Range("A2:A10")

Thanks for your help

How did you filter in the first place? It sounds like you want SUMIF
with two variables. A SUMPRODUCT might be the easiest way, let your
formula do the filtering.

Otherwise, SUBTOTAL will ignore hidden rows whereas other SUM
functions will not. If you can wrap SUBTOTAL around the IF, it should
work.