Calculate FREQUENCY based on criteria...
 

Hi there -
Stumped on this one... I want to calculate frequency for items that
meet a certain criteria. In this case, date. I have a bunch of data that
looks like this:

COL-A | COL-B
1/2/09 | 10
1/6/09 | 13
1/8/09 | 10
10/23/08 | 10
2/3/09 | 10
2/9/09 | 10

I want to calculate the FREQUENCY of COL-B data for items that occur in
2009. In the example above, that would be 4 for "10" and 1 for "13".

I'm using this data to create a frequency distribution graph.

thanks in advance!
-meat

Calculate FREQUENCY based on criteria...
 

You are counting occurances based upon TWO criteria, date and value.

Whenever you are faced with more than one criteria, consider using
SUMPRODUCT()

=SUMPRODUCT((YEAR(A1:A1000)=2009)*(B1:B1000=10))


change the year and value as you desire. There is a really good write-up on
SUMPRODUCT in:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200831


"MeatLightning" wrote:

Hi there -
Stumped on this one... I want to calculate frequency for items that
meet a certain criteria. In this case, date. I have a bunch of data that
looks like this:

COL-A | COL-B
1/2/09 | 10
1/6/09 | 13
1/8/09 | 10
10/23/08 | 10
2/3/09 | 10
2/9/09 | 10

I want to calculate the FREQUENCY of COL-B data for items that occur in
2009. In the example above, that would be 4 for "10" and 1 for "13".

I'm using this data to create a frequency distribution graph.

thanks in advance!
-meat

Calculate FREQUENCY based on criteria...
 

Sure, that works. FREQUENCY is cleaner on it's own as it sorts out the bin
segments for you... but so it goes with beggars and choosers and such...

thanks!!

"Gary''s Student" wrote:

You are counting occurances based upon TWO criteria, date and value.

Whenever you are faced with more than one criteria, consider using
SUMPRODUCT()

=SUMPRODUCT((YEAR(A1:A1000)=2009)*(B1:B1000=10))


change the year and value as you desire. There is a really good write-up on
SUMPRODUCT in:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200831


"MeatLightning" wrote:

Hi there -
Stumped on this one... I want to calculate frequency for items that
meet a certain criteria. In this case, date. I have a bunch of data that
looks like this:

COL-A | COL-B
1/2/09 | 10
1/6/09 | 13
1/8/09 | 10
10/23/08 | 10
2/3/09 | 10
2/9/09 | 10

I want to calculate the FREQUENCY of COL-B data for items that occur in
2009. In the example above, that would be 4 for "10" and 1 for "13".

I'm using this data to create a frequency distribution graph.

thanks in advance!
-meat

Calculate FREQUENCY based on criteria...
 

As I wrote elsewhere, I like array formulas.

to count frequency of 10 in 2009, type for instance in cell c10:
=sum((year(a2:a6)=2009)*(b2:b6=10)) then press ctrl+shift+enter

the easiest way is to use Countif formula



"MeatLightning" wrote:

Hi there -
Stumped on this one... I want to calculate frequency for items that
meet a certain criteria. In this case, date. I have a bunch of data that
looks like this:

COL-A | COL-B
1/2/09 | 10
1/6/09 | 13
1/8/09 | 10
10/23/08 | 10
2/3/09 | 10
2/9/09 | 10

I want to calculate the FREQUENCY of COL-B data for items that occur in
2009. In the example above, that would be 4 for "10" and 1 for "13".

I'm using this data to create a frequency distribution graph.

thanks in advance!
-meat

Calculate FREQUENCY based on criteria...
 

oops, sorry, Countif cannot work with two criteria, my array formula does.

"Alojz" wrote:

As I wrote elsewhere, I like array formulas.

to count frequency of 10 in 2009, type for instance in cell c10:
=sum((year(a2:a6)=2009)*(b2:b6=10)) then press ctrl+shift+enter

the easiest way is to use Countif formula



"MeatLightning" wrote:

Hi there -
Stumped on this one... I want to calculate frequency for items that
meet a certain criteria. In this case, date. I have a bunch of data that
looks like this:

COL-A | COL-B
1/2/09 | 10
1/6/09 | 13
1/8/09 | 10
10/23/08 | 10
2/3/09 | 10
2/9/09 | 10

I want to calculate the FREQUENCY of COL-B data for items that occur in
2009. In the example above, that would be 4 for "10" and 1 for "13".

I'm using this data to create a frequency distribution graph.

thanks in advance!
-meat