sumif countif thing?

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.

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!

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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)),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+ENTER

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]...
>
> 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)),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+ENTER
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=562529
>