counting cells in two columns that have the same results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 17 May 2006 23:44 GMT

I'm trying to come up with a formula that would give me a count of a yes/no
combination in two different columns like below:

K L
1 Member Approved
2 y y
3 y n
4 n y
5 y y

where I can get a count of the "Y" in column K that are also "Y" in column
L, in other words, the members who are also approved. I've tried stringing
two countif formulae together, but can't seem to get that to work. Any
suggestions?

Reply to this Message

Ron Coderre - 18 May 2006 00:21 GMT

Chris........Try this:

=SUMPRODUCT((K2:K100="Y")*(L2:L100="Y"))

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> I'm trying to come up with a formula that would give me a count of a yes/no
> combination in two different columns like below:
[quoted text clipped - 10 lines]
> two countif formulae together, but can't seem to get that to work. Any
> suggestions?

Reply to this Message

Chris - 18 May 2006 20:30 GMT

I get an error that stats value not available. I copied the formula and then
simply adjusted the ranges. Does it matter if we're trying to use a
"sumproduct" formula and we're dealing with text? I'm trying to evaluate two
columns of text and they both have to be a "y" to be counted. Thanks.

> Chris........Try this:
>
[quoted text clipped - 23 lines]
> > two countif formulae together, but can't seem to get that to work. Any
> > suggestions?

Reply to this Message

Ron Coderre - 18 May 2006 20:43 GMT

Chris:

Please post your formula so we can see what structure you're using.

***********
Regards,
Ron

XL2002, WinXP

> I get an error that stats value not available. I copied the formula and then
> simply adjusted the ranges. Does it matter if we're trying to use a
[quoted text clipped - 28 lines]
> > > two countif formulae together, but can't seem to get that to work. Any
> > > suggestions?

Reply to this Message

Chris - 18 May 2006 21:19 GMT

OMG.....it works!!! Thank you, thank you, thank you! :)

> Chris........Try this:
>
[quoted text clipped - 23 lines]
> > two countif formulae together, but can't seem to get that to work. Any
> > suggestions?

Reply to this Message

Ashish Mathur - 18 May 2006 00:37 GMT

Hi,

Although sumproduct is definitely a better formula to use, you cn also use
an array formula (Ctrl+Shiuft+Enter)

=sum(if((rangeK="y")*(rangeL="y"),1,0))

Regards,

Ashish Mathur

Reply to this Message

Chris - 18 May 2006 20:38 GMT

Tried it. Did not get an error message, but continually got a "0" answer
regardless of the combination of "Y"s and "N"s in each of the columns.

> Hi,
>
[quoted text clipped - 21 lines]
> > two countif formulae together, but can't seem to get that to work. Any
> > suggestions?

Reply to this Message

RaymundCG - 18 May 2006 06:33 GMT

Hi Chris!

You may try this (I have placed the y-n data in A2:B5 range to test them)...

=SUM(IF(A2:A5&B2:B5="yy",1,0))

enter the formula as an array formula (using CTRL+SHIFT+ENTER). You may
modify the ranges as appropriate.

Hope this helps!

Signature

Thanks and kind regards

Reply to this Message

Chris - 18 May 2006 20:40 GMT

Returns a value of 0 in every case regardless of the combinations of "Y"s and
"N"s in each of the respective columns.

> Hi Chris!
>
[quoted text clipped - 21 lines]
> > two countif formulae together, but can't seem to get that to work. Any
> > suggestions?

Reply to this Message