Count if function

[scorpy_1511]

Hi..

I have this below problem.

Cell A B
1 GEN 10, 11
2 GEN 12
3 GEN 12, 10
4 ENG 10
5 ENG 11
6 ENG 10, 13
7 LOG 11
8 LOG 11
9 GEN 10
10 LOG 10

If I'd like to count how many cell in B1:B10 that below to GEN ( see
A1:A10) and contain '10' in its cell.
How can I use countif formula with this?

Thanks for ur help:-D

 

[JE McGimpsey]

I have this below problem.

Cell A B
1 GEN 10, 11
2 GEN 12
3 GEN 12, 10
4 ENG 10
5 ENG 11
6 ENG 10, 13
7 LOG 11
8 LOG 11
9 GEN 10
10 LOG 10

If I'd like to count how many cell in B1:B10 that below to GEN ( see
A1:A10) and contain '10' in its cell.
How can I use countif formula with this?

You can't use COUNTIF(). One alternative:

=SUMPRODUCT(--(A1:A10="GEN"),--ISNUMBER(FIND("10",B1:B10)))

 

[Domenic]

Try...

=SUMPRODUCT(--($A$1:$A$10="GEN"),--ISNUMBER(FIND(10,$B$1:$B$10)))

Hope this helps!

 

[Bob Greenblatt]

Hi..

I have this below problem.

Cell A B
1 GEN 10, 11
2 GEN 12
3 GEN 12, 10
4 ENG 10
5 ENG 11
6 ENG 10, 13
7 LOG 11
8 LOG 11
9 GEN 10
10 LOG 10

If I'd like to count how many cell in B1:B10 that below to GEN ( see
A1:A10) and contain '10' in its cell.
How can I use countif formula with this?

Thanks for ur help:-D

The problem is the string values in column B. You can't do this directly
with countif. What you could do, is assign the range as a database and then
use Dcounta. For example, insert a row, and place a field name like "Type"
in A1, and "Value in B1. Then your mini-database goes from A1:B11. Some
where else, set up your criteria. For example in D1:E3, your criteria would
look like:

D1 E1
Type Value
GEN 10
GEN *10*

Then your formula would look like: =dcounta(a1:b11,"Type",D1:E3)

You need 2 criteria as 10 looks only for the numeric value, and *10* looks
for the string value and will not find 10. So the dual criteria will find
both instances. But, be careful, *10* will also find 100, 0r 110, etc.