countif function
 

Hi,

I have a worksheet of data that I'm trying to extract particular values
from.

Column J contains the name of the quarter eg "Q1 2005-6" I've used
COUNTIF to calculate how many records meet that criteria.

In another cell, I'd like to narrow my search with additional
criteria.

Column L contains numerical values. I would like to be able to
calculate how many of the "Q1 2005-6" records meet certain criteria in
the L column. For example how many of them contain a value of less than
"32".

I can work out each value seperately but would like to be able to
create a function that allows me to set both criteria to get the result
neatly in one cell.

Can anyone make any suggestions?

Thanks


--
a haigh
------------------------------------------------------------------------
a haigh's Profile: http://www.excelforum.com/member.php...o&userid=32897
View this thread: http://www.excelforum.com/showthread...hreadid=527093

countif function
 

=SUMPRODUCT(--(J2:J17="Q1 2005-6"),--(K2:K17<32))

Adjust the end of the range to your real values!

Regards,
Stefi

€ža haigh€� ezt Ã*rta:


Hi,

I have a worksheet of data that I'm trying to extract particular values
from.

Column J contains the name of the quarter eg "Q1 2005-6" I've used
COUNTIF to calculate how many records meet that criteria.

In another cell, I'd like to narrow my search with additional
criteria.

Column L contains numerical values. I would like to be able to
calculate how many of the "Q1 2005-6" records meet certain criteria in
the L column. For example how many of them contain a value of less than
"32".

I can work out each value seperately but would like to be able to
create a function that allows me to set both criteria to get the result
neatly in one cell.

Can anyone make any suggestions?

Thanks


--
a haigh
------------------------------------------------------------------------
a haigh's Profile: http://www.excelforum.com/member.php...o&userid=32897
View this thread: http://www.excelforum.com/showthread...hreadid=527093

countif function
 

Try
=SUMPRODUCT(--(J2:J1000="Q1 2005-6"),--(L2:L1000<32))


"a haigh" wrote in
message ...

Hi,

I have a worksheet of data that I'm trying to extract particular values
from.

Column J contains the name of the quarter eg "Q1 2005-6" I've used
COUNTIF to calculate how many records meet that criteria.

In another cell, I'd like to narrow my search with additional
criteria.

Column L contains numerical values. I would like to be able to
calculate how many of the "Q1 2005-6" records meet certain criteria in
the L column. For example how many of them contain a value of less than
"32".

I can work out each value seperately but would like to be able to
create a function that allows me to set both criteria to get the result
neatly in one cell.

Can anyone make any suggestions?

Thanks


--
a haigh
------------------------------------------------------------------------
a haigh's Profile:
http://www.excelforum.com/member.php...o&userid=32897
View this thread: http://www.excelforum.com/showthread...hreadid=527093

countif function
 

thanks very much.


--
a haigh
------------------------------------------------------------------------
a haigh's Profile: http://www.excelforum.com/member.php...o&userid=32897
View this thread: http://www.excelforum.com/showthread...hreadid=527093