counting cells using multiple criteria
 

Hi,
how do a create a custom function to count cells (text) based on more
than criteria. Example

column a - lists critiera 1 (text)
column b - list criteria 2 (text)

I would like to be able to count number of cells from colulmn b that
match citieria 1 and 2.

I have tried multiple countif and counta and sum product but not
getting the correct result


--
st120869
------------------------------------------------------------------------
st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330
View this thread: http://www.excelforum.com/showthread...hreadid=517741

counting cells using multiple criteria
 

=sumproduct(--(rng1="text1"),--(rng2="text2"))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"st120869" wrote in
message ...

Hi,
how do a create a custom function to count cells (text) based on more
than criteria. Example

column a - lists critiera 1 (text)
column b - list criteria 2 (text)

I would like to be able to count number of cells from colulmn b that
match citieria 1 and 2.

I have tried multiple countif and counta and sum product but not
getting the correct result


--
st120869
------------------------------------------------------------------------
st120869's Profile:
http://www.excelforum.com/member.php...o&userid=18330
View this thread: http://www.excelforum.com/showthread...hreadid=517741

counting cells using multiple criteria
 

THanks, I get a #value error.

I ahve include some text below
Reward no
Reward no
Reward Yes
Reward Yes
Reward Yes
Reward Yes
Reward Yes
Reward Yes
xx1 Yes

the result I would expect to see if criteria is column A ="reward" &
column B = "yes" would be 7


--
st120869
------------------------------------------------------------------------
st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330
View this thread: http://www.excelforum.com/showthread...hreadid=517741

counting cells using multiple criteria
 

=SUMPRODUCT(--(A1:A100="Reward"),--(B1:B100="Yes"))

works for me, but with a result of 6

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"st120869" wrote in
message ...

THanks, I get a #value error.

I ahve include some text below
Reward no
Reward no
Reward Yes
Reward Yes
Reward Yes
Reward Yes
Reward Yes
Reward Yes
xx1 Yes

the result I would expect to see if criteria is column A ="reward" &
column B = "yes" would be 7


--
st120869
------------------------------------------------------------------------
st120869's Profile:
http://www.excelforum.com/member.php...o&userid=18330
View this thread: http://www.excelforum.com/showthread...hreadid=517741

counting cells using multiple criteria
 

Bob,
your maths is better than mine !!, I have recopied your formula but
still getting a #value error - any clues??


--
st120869
------------------------------------------------------------------------
st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330
View this thread: http://www.excelforum.com/showthread...hreadid=517741

counting cells using multiple criteria
 

Not really, can you post the workbook somewhere?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"st120869" wrote in
message ...

Bob,
your maths is better than mine !!, I have recopied your formula but
still getting a #value error - any clues??


--
st120869
------------------------------------------------------------------------
st120869's Profile:
http://www.excelforum.com/member.php...o&userid=18330
View this thread: http://www.excelforum.com/showthread...hreadid=517741