Hi,

I have been trying to figure this out with no luck. I am trying to
count the number of occurences that "textvalue1" appears on the same
row with "textvalue2". Both "textvalue1" and "textvalue2" appear in
random areas of a cell range (c2:n999). Essentially I would like to do
something like this:

=COUNTIF(c2:n999,"textvalue1")AND IF
=COUNTIF(c2:n999,"textvalue2")

and then display the number of occurences that both textvalue1 and
textvalue2 appear in the same row together? Is this possible with a
cell range like this or should I just stop.

Thanks.


--
5dolla
------------------------------------------------------------------------
5dolla's Profile: http://www.excelforum.com/member.php...o&userid=36493
View this thread: http://www.excelforum.com/showthread...hreadid=562529

Try this UDF (as I can't think of a formula solution!):

Place in required cell:

=countxx(C2:N999,"Textvalue1","Textvalue2")

Put code in general module

==Alt + F11 to open Visual Basic Editor
== right click on VBA project (for your w/book) and Insert==module
Copy/paste code below

HTH

-------------------------------------------------------------------------------

Function countxx(ByRef rng As Range, ByVal fval1 As String, ByVal fval2 As
String) As Long
n = 0
For r = 1 To rng.Rows.Count
n1 = Application.Match(fval1, Range(rng(r, 1), rng(r,
rng.Columns.Count)), 0)
n2 = Application.Match(fval2, Range(rng(r, 1), rng(r,
rng.Columns.Count)), 0)
If Not IsError(n1) Then
If Not IsError(n2) Then
n = n + 1
End If
End If
Next r
countxx = n
End Function

"5dolla" wrote:


Hi,

I have been trying to figure this out with no luck. I am trying to
count the number of occurences that "textvalue1" appears on the same
row with "textvalue2". Both "textvalue1" and "textvalue2" appear in
random areas of a cell range (c2:n999). Essentially I would like to do
something like this:

=COUNTIF(c2:n999,"textvalue1")AND IF
=COUNTIF(c2:n999,"textvalue2")

and then display the number of occurences that both textvalue1 and
textvalue2 appear in the same row together? Is this possible with a
cell range like this or should I just stop.

Thanks.


--
5dolla
------------------------------------------------------------------------
5dolla's Profile: http://www.excelforum.com/member.php...o&userid=36493
View this thread: http://www.excelforum.com/showthread...hreadid=562529

OMG!!! That's totally genius and you probably just whipped that off the
top of your head, frickin' amazing.

Thank-you soooooo much Toppers. It's a good thing your not here b/c I
would have to kiss you............you totally rule! :)


--
5dolla
------------------------------------------------------------------------
5dolla's Profile: http://www.excelforum.com/member.php...o&userid=36493
View this thread: http://www.excelforum.com/showthread...hreadid=562529

This may work, if you don't want to use VBA:
=SUM(COUNTIF(OFFSET(C1,ROW(INDIRECT("1:998")),0,1, 12),"=textvalue1")*COUNTIF(OFFSET(C1,ROW(INDIRECT( "1:998")),0,1,12),"=textvalue2"))

The formula has to be array-entered (hold down the control and shift keys
then press enter)

"5dolla" wrote in
message ...

Hi,

I have been trying to figure this out with no luck. I am trying to
count the number of occurences that "textvalue1" appears on the same
row with "textvalue2". Both "textvalue1" and "textvalue2" appear in
random areas of a cell range (c2:n999). Essentially I would like to do
something like this:

=COUNTIF(c2:n999,"textvalue1")AND IF
=COUNTIF(c2:n999,"textvalue2")

and then display the number of occurences that both textvalue1 and
textvalue2 appear in the same row together? Is this possible with a
cell range like this or should I just stop.

Thanks.


--
5dolla
------------------------------------------------------------------------
5dolla's Profile:
http://www.excelforum.com/member.php...o&userid=36493
View this thread: http://www.excelforum.com/showthread...hreadid=562529

Thanks Jack Sheet, I will definitely give that a shot in my next
worksheet, I have 3 moe that I have to perform this on.

There seems to be so many short formulas to do more complicated things
than what I'm doing, it's wierd that the formulas to count the number
of rows that a value occurs multiple times would require so much, but
screw it, it works so I'm happy and grateful beyond words that I don't
have to sit and manually do a find then count the occurrences.

You folk are FN brilliant!

Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you
Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you


--
5dolla
------------------------------------------------------------------------
5dolla's Profile: http://www.excelforum.com/member.php...o&userid=36493
View this thread: http://www.excelforum.com/showthread...hreadid=562529

Thanks for the feedback.

I would use Jack's solution as it uses standard functions (... so thanks
also to you Jack for filling in another gap in my knowledge).

"5dolla" wrote:


Thanks Jack Sheet, I will definitely give that a shot in my next
worksheet, I have 3 moe that I have to perform this on.

There seems to be so many short formulas to do more complicated things
than what I'm doing, it's wierd that the formulas to count the number
of rows that a value occurs multiple times would require so much, but
screw it, it works so I'm happy and grateful beyond words that I don't
have to sit and manually do a find then count the occurrences.

You folk are FN brilliant!

Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you
Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you


--
5dolla
------------------------------------------------------------------------
5dolla's Profile: http://www.excelforum.com/member.php...o&userid=36493
View this thread: http://www.excelforum.com/showthread...hreadid=562529

I would not be surprised if there isn't a much more elegant solution than
mine. Someone will doubtless post one

"5dolla" wrote in
message ...

Thanks Jack Sheet, I will definitely give that a shot in my next
worksheet, I have 3 moe that I have to perform this on.

There seems to be so many short formulas to do more complicated things
than what I'm doing, it's wierd that the formulas to count the number
of rows that a value occurs multiple times would require so much, but
screw it, it works so I'm happy and grateful beyond words that I don't
have to sit and manually do a find then count the occurrences.

You folk are FN brilliant!

Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you
Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you Thank-you


--
5dolla
------------------------------------------------------------------------
5dolla's Profile:
http://www.excelforum.com/member.php...o&userid=36493
View this thread: http://www.excelforum.com/showthread...hreadid=562529

Hi Jack,

I think there's a potential problem with your solution. If textvalue1
appears twice in a single row in which textvalue 2 also appears that
will be counted twice.

To combat that you could use this formula

=COUNT(1/(FREQUENCY(IF(C2:N999="textvalue1",ROW(C2:N999)),R OW(C2:C999)-ROW(C$2)+1)*(FREQUENCY(IF(C2:N999="textvalue2",ROW (C2:N999)),ROW(C2:C999)-ROW(C$2)+1))))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=562529

Good Catch.
My Bad.

My solution could have been changed only very slightly to correct for that:

=SUM((COUNTIF(OFFSET(C1,ROW(INDIRECT("1:998")),0,1 ,12),"=textvalue1")0)*(COUNTIF(OFFSET(C1,ROW(INDI RECT("1:998")),0,1,12),"=textvalue2")0))

But your solution is more elegant even so.

Apologies if this has been posted multiple times.


"daddylonglegs"
wrote in message
news:daddylonglegs.2b5t6v_1153257008.7595@excelfor um-nospam.com...

Hi Jack,

I think there's a potential problem with your solution. If textvalue1
appears twice in a single row in which textvalue 2 also appears that
will be counted twice.

To combat that you could use this formula

=COUNT(1/(FREQUENCY(IF(C2:N999="textvalue1",ROW(C2:N999)),R OW(C2:C999)-ROW(C$2)+1)*(FREQUENCY(IF(C2:N999="textvalue2",ROW (C2:N999)),ROW(C2:C999)-ROW(C$2)+1))))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=562529