Countif Function Question

[mjensen]

Using the "countif" function I am able to count the number of cells tha
are less than or equal to 10. However, I also need to be able to coun
the number of cells that are less than or equal to 10 which have th
letter T in front of the number.

A
1 56
2 T18
3 2
4 T5
5 8
6 10
7 T3
9 36
10 T10

Please help me out.
Thank you,
Mat

 

[Frank Kabel]

Hi
any chance you can split this data in two columns. That is the letter
in column A and the number in column B?

 

[Aladin Akyurek]

=COUNT(IF(--REPLACE(A1:A10,1,--(LEFT(A1:A10)="T"),"")<=10,1))

which you need to confirm with control+shift+enter instead of just with
enter.

 

[tori]

I have a COUNTIF question that's similar to this so I thought I may a
well post it in this thread. I want to count the number of rows tha
contain a certain string in column and A and a value greater than 0 i
column B.

This will count the number of instances of "ABC" in column A

=COUNTIF(A:A, “ABC")

And this will count the number of rows with a value greater than 0 i
column B

=COUNTIF(B:B, ">0")

However, I have no idea how to combine these two to count the number o
rows at contain “ABC” in column A AND a value “>0” in column B. I wan
it so the result woudl be 2 with the below data because there's tw
instances of "ABC" in colum A with a value great than 0 in colum B.

ABC 25
DEF 32
ABC -57
HIJ -83
ABC 18
HIJ 12

From reading other threads on similar topics I thought this migh
work, but I don’t understand why and it doesn’t work anyway.

=SUMPRODUCT((A:A=”ABC”)*(B:B=">0"))


My brain has turned into a mulch of jelly after being unable to find
solution for hours, so I’m extremely grateful for any help you ca
offer

 

[duane]

=SUMPRODUCT((A:A=”ABC”)*(B:B>0))

should do i

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((A1:A1000=”ABC”)*(B1:B1000>>0))

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:$A$100=E2),--($B$2:$B$200>0))

where E2 houses a condition like ABC.

Note 1. This type of formulas do not admit whole column references like A:A.

Note 2. If you happen to have text, even a formula blank (i.e., ""), you
need to switch to:

=SUMPRODUCT(--($A$2:$A$100=E2),--($B$2:$B$200>0),--ISNUMBER($B$2:$B$200))

 

[Frank Kabel]

Hi
typo. Make this:
=SUMPRODUCT((A1:A1000=”ABC”)*(B1:B1000>0))

 

[tori]

Thanks a lot for the help.

I tried

=SUMPRODUCT((A:A=”ABC”)*(B:B>0))

but for some reason it doesn't work. However

=SUMPRODUCT((A1:A65536=”ABC”)*(B1:B65536>0))

works great.

Thanks again. I was almost at the point of adding things up manuall
every time I updated the spreadsheet :

 

[Aladin Akyurek]

=SUMPRODUCT((A1:A65536="ABC")*(B1:B65536>0))

That shouldn't work either.

See my reply.