Hi,

I am trying to devise a formula that looks down a set of data, and look in
columns A and B for Year/Quarter and for each returns a count of whether
colum C is 10.
I can easily get to to count values 10 but not for those that occur in
specific Quarters.

Thanks for any and all help

Data looks as follows:

Yr Q No. of Cases
2008 1 10
2008 1 20
2008 1 30
2008 1 30
2008 1 30
2008 1 10
2008 1 10
2008 2 30
2008 2 30
2008 2 30
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 3 30
2008 3 30
2008 3 30
2008 3 20
2008 3 20
2008 4 20
2008 4 20
2007 1 10
2007 1 30
2007 1 30
2007 2 30
2007 2 10
2007 2 10
2007 2 10
2007 3 10
2007 4 10
2007 4 10
2007 4 10
2007 4 30
2007 4 30

Hi,

I've assumed that the years and quarters are numbers and not dates

=SUMPRODUCT((A2:A37=2008)*(B2:B37=1)*(C2:C3710))

In practice I'd use cell references for the year and date

=SUMPRODUCT((A2:A37=H1)*(B2:B37=H2)*(C2:C3710))

Mike

"Miketsw" wrote:

Hi,

I am trying to devise a formula that looks down a set of data, and look in
columns A and B for Year/Quarter and for each returns a count of whether
colum C is 10.
I can easily get to to count values 10 but not for those that occur in
specific Quarters.

Thanks for any and all help

Data looks as follows:

Yr Q No. of Cases
2008 1 10
2008 1 20
2008 1 30
2008 1 30
2008 1 30
2008 1 10
2008 1 10
2008 2 30
2008 2 30
2008 2 30
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 3 30
2008 3 30
2008 3 30
2008 3 20
2008 3 20
2008 4 20
2008 4 20
2007 1 10
2007 1 30
2007 1 30
2007 2 30
2007 2 10
2007 2 10
2007 2 10
2007 3 10
2007 4 10
2007 4 10
2007 4 10
2007 4 30
2007 4 30

That worked wonders - thats saved me a few hours every week

Mike


"Mike H" wrote:

Hi,

I've assumed that the years and quarters are numbers and not dates

=SUMPRODUCT((A2:A37=2008)*(B2:B37=1)*(C2:C3710))

In practice I'd use cell references for the year and date

=SUMPRODUCT((A2:A37=H1)*(B2:B37=H2)*(C2:C3710))

Mike

"Miketsw" wrote:

Hi,

I am trying to devise a formula that looks down a set of data, and look in
columns A and B for Year/Quarter and for each returns a count of whether
colum C is 10.
I can easily get to to count values 10 but not for those that occur in
specific Quarters.

Thanks for any and all help

Data looks as follows:

Yr Q No. of Cases
2008 1 10
2008 1 20
2008 1 30
2008 1 30
2008 1 30
2008 1 10
2008 1 10
2008 2 30
2008 2 30
2008 2 30
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 3 30
2008 3 30
2008 3 30
2008 3 20
2008 3 20
2008 4 20
2008 4 20
2007 1 10
2007 1 30
2007 1 30
2007 2 30
2007 2 10
2007 2 10
2007 2 10
2007 3 10
2007 4 10
2007 4 10
2007 4 10
2007 4 30
2007 4 30

Glad I could help

"Miketsw" wrote:

That worked wonders - thats saved me a few hours every week

Mike


"Mike H" wrote:

Hi,

I've assumed that the years and quarters are numbers and not dates

=SUMPRODUCT((A2:A37=2008)*(B2:B37=1)*(C2:C3710))

In practice I'd use cell references for the year and date

=SUMPRODUCT((A2:A37=H1)*(B2:B37=H2)*(C2:C3710))

Mike

"Miketsw" wrote:

Hi,

I am trying to devise a formula that looks down a set of data, and look in
columns A and B for Year/Quarter and for each returns a count of whether
colum C is 10.
I can easily get to to count values 10 but not for those that occur in
specific Quarters.

Thanks for any and all help

Data looks as follows:

Yr Q No. of Cases
2008 1 10
2008 1 20
2008 1 30
2008 1 30
2008 1 30
2008 1 10
2008 1 10
2008 2 30
2008 2 30
2008 2 30
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 2 10
2008 3 30
2008 3 30
2008 3 30
2008 3 20
2008 3 20
2008 4 20
2008 4 20
2007 1 10
2007 1 30
2007 1 30
2007 2 30
2007 2 10
2007 2 10
2007 2 10
2007 3 10
2007 4 10
2007 4 10
2007 4 10
2007 4 30
2007 4 30