sumif countif thing?

[5dolla]

Hi,

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

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

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

Thanks

 

[Guest]

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]

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!

 

[Jack Sheet]

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]

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

 

[Guest]

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).

 

[Jack Sheet]

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

 

[daddylonglegs]

Hi Jack,

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

To combat that you could use this formula

=COUNT(1/(FREQUENCY(IF(C2:N999="textvalue1",ROW(C2:N999)),ROW(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+ENTE

 

[Jack Sheet]

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(INDIRECT("1:998")),0,1,12),"=textvalue2")>0))

But your solution is more elegant even so.

Apologies if this has been posted multiple times.


"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...