Count specific number in a table when given number appears in same row (modified)

[Karnik]

Hi Experts,
In a given table, when specific number occurs, find another given number that occurred along with specific number in row same time.

choosing data and filters consume too much time !

File attached (excel 2007 revised on 3May






1 1 6 6 6 8 9
2 2 5 7 7 8 9
3 2 6 7 8 9 9
4 1 3 5 6 8 9
5 2 5 5 6 6 9
6 1 1 1 6 7 8
7 5 6 7 7 9 9
8 4 4 5 5 6 8
9 1 2 4 5 5 6
10 6 7 7 7 8 9

task:
If only 6 present in a row from B1:G10 then count total number "8" in rows 1 to 10
(following is result manually counted)
Count number of 8 when 6 appears in same row from table
Final Results:
If following No is present =6 Count 8 total =6






Thanks in advance
Karnik

[AlKey]

Maybe this

=IF(COUNTIF(B1:G10,6)>0,COUNTIF(B1:G10,8),"")

[Glenn Kennedy]

Alkey, I've been looking at this off and on for a while now. My own meanderings are taking me into very overcomplicated areas!! I don't think that yours is there yet, as it counts the 8 in F2, which is in a row without a 6.

[Karnik]

Hi Alkey,
May be I have confused and not clear
Your suggestion counts all number 8 in the given table.
I am seeking, eg. if 6 is present THEN count 8 number from the row. If 6 is not present and 8 is in the row then 8 is not counted or dropped from that row
Thanks
Karnik

[AlKey]

Ok

in H1 and copy down

=IF(COUNTIF(B1:G1,6)>0,COUNTIF(B$1:G$10,8),"")

[Karnik]

Hi Glenn
Thanks for your visit
Regards
Karnik

[Glenn Kennedy]

Karnik, in your example, shoouldn't the answer be 6, not 7....

[AlKey]

i guess I should've been this

In H1 and copy down

=IF(COUNTIF(B1:G1,6)>0,COUNTIF(B1:G1,8),0)

and yes, the answer should be 6

[Karnik]

Hi Alkey and Glenn,
Yes it should be 6,
Alkey your solution works if copied to last row and again counting total
Ideally I had planned to replace by cell number to change to required digit.
Regards
Karnik

[Karnik]

Hi Experts,
file also edited and shown in main file
Please expert
Karnik

[AlKey]

The number is replaced with cell reference J$2

in H1 and copy down

=IF(COUNTIF(B1:G1,J$2)>0,COUNTIF(B1:G1,8),0)

in M2 enter sum formula

=SUM(H1:H10)

[Karnik]

Hi Alkey,
Thanks for looking in to it, your suggestion works , as data runs in 5266 rows it becomes too cumbersome copy in all rows.
Any way we can put your function in Array to make it fast/simple? or VB (I am too nervous!)

Regards
Karnik

[AlKey]

You only have to copy it once and all you have to do is to change the number in J2. Since you have to examine each row, I don't really see any other option. Maybe someone else will take a look and offer a better solution.

[Karnik]

Hello Expert,

Forum Expert Alkey has brilliantly suggested function to out fish two numbers when appear in a row.
Now I have been given task to fish out three numbers when (all three) appear in a row.
correct function for 2 numbers by forum Expert Alkey is:

=IF(COUNTIF($B10:$G10,$H$2)>0,COUNTIF($B10:$G10,$I$2),0)

My modified function that is incorrect is:

=IF(and(COUNTIF($B10:$G10,$H$2)>0,AND(COUNTIF($B10:$G10,$I$2)>0,AND(COUNTIF($B10:$G10,$J$2)),0))

I have been struggling to modify correctly , but no luck, I have attached file again for kind perusal,

Thanks in advance

Karnik

[Karnik]

Hi Experts,
My function formula is in correct as follows,
Please connect it!
For details look in to attachment or trails of below

=IF(and(COUNTIF($B10:$G10,$H$2)>0,AND(COUNTIF($B10:$G10,$I$2)>0,AND(COUNTIF($B10:$G10,$J$2)),0))

Thanks
Karnik

[Glenn Kennedy]

Is this what you had in mind?

[Glenn Kennedy]

Something odd here. I have replied, but I can't access the reply. So, I'll try again.

Is this what you had in mind?