Formula Help !

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

How to put this in a formula ?
e.g. Worksheet range A1:F2

A B C D E F
1 2 3 4 5 6
7 8 9 10 11 12

In G1 I like to count if the range A1:F1 contain a "1" then count range
A2:F2 which contain 5,7,8.
In another word , in G1 the count will be 2.

Thank you.
 
I think G1 should show 1, not 2, given the sample you provided:

=SUMPRODUCT((A1:F1=1)*ISNUMBER(MATCH(A2:F2,{5,7,8},0)))
 
How to put this in a formula ?
e.g. Worksheet range A1:F2

A B C D E F
1 2 3 4 5 6
7 8 9 10 11 12

In G1 I like to count if the range A1:F1 contain a "1" then count range
A2:F2 which contain 5,7,8.
In another word , in G1 the count will be 2.

Thank you.
Click to expand...


=(COUNTIF(A1:F1,1)>0)*COUNT(MATCH({5,7,8},A2:F2,0))


--ron
 
Thank you for the formula, Ron Rosenfed but G1 should be 1 as there are
only 7 & 8 in A2:F2
Regards
 
Michael168 said:
Thank you for the formula, Ron Rosenfed but G1 should be 1 as there are
only 7 & 8 in A2:F2
Click to expand...

Isn't it about time to explain your counting procedure?
 
Hello Ron Rosenfeld,

The formula also give me the wrong count when range in A1:F1 contains
anything that start with a '1' in front like 17,18,19 and so on. The
fromula treat 17,18 etc.. as '1' also.

How to make the '1' unique?

Try this in range A1:F2 with
=(COUNTIF(A1:F1,1)>0)*COUNT(MATCH({5,7,8},A2:F2,0))


4 6 9 17 18 19
7 8 9 10 11 12

For this example, the count should be zero.
Regards.
 
Aladin Akyurek said:
Isn't it about time to explain your counting procedure?
Click to expand...

Especially as the OP wrote in the original post "In another word , in G1 the
count will be 2."

Maybe there's an unmentioned half life involved.
 
Hello Ron Rosenfeld,

The formula also give me the wrong count when range in A1:F1 contains
anything that start with a '1' in front like 17,18,19 and so on. The
fromula treat 17,18 etc.. as '1' also.

How to make the '1' unique?

Try this in range A1:F2 with
=(COUNTIF(A1:F1,1)>0)*COUNT(MATCH({5,7,8},A2:F2,0))


4 6 9 17 18 19
7 8 9 10 11 12

For this example, the count should be zero.
Regards.
Click to expand...

I'm having trouble understanding what you want.

In your first post, you said:
In G1 I like to count if the range A1:F1 contain a "1" then count range
A2:F2 which contain 5,7,8.
In another word , in G1 the count will be 2.
Click to expand...

Then you say:
but G1 should be 1 as there are only 7 & 8 in A2:F2
Click to expand...

So how do you want to count the 5,7 and 8 in A2:F2? G1 cannot be both 1 and 2
.....

===================

So far as the data you give in this post, the formula I gave you DOES give a
count of zero. What result do you want?


--ron
 
Back
Top