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

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

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

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

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