Only Countif value in cell A matches cell B ? Closed

Question

Hello, 

I am trying to calculate the following, 
if Cell C:C has any values of less than <0

and also match specific text in Cell I:I
I have the below but it still doesn't work ?
=countif(C:C,"<0="&I:I,"#N/A")

so to sum up, I am wanting number of accounts in cell C:C that match text in Cell I:I

any Ideas ?
thanks

Configuration: Windows XP / Firefox 3.6.10

venkat1926 · Answer

For your question "if Cell C:C has any values of less than <0"

=COUNTIF(C:C,"<0")
note the quotations marks.

Let me rephrase your second question

Count if the following conditions are satisfied 

Col c is <0
Col C is equivalent to corresponding value in the same row but in column I
The third condition  is not clear. Should it not be "<>#N/A"
Why this condition. needs clarification 

for multiple conditions use sumproduct function 

try this type of formula 

=SUMPRODUCT((C1:C16<0)*(C1:C16=I1:I16))

modify t suit you particularly the last row C16 and I 16

venkat1926 · Answer

that sumproduct formula of yours is not correct 
what is after all @N/A. This is error due to some formula 

if you have some of coloumnA is negative, positive and error as mentioned above 
when you want to count only negative that will not take into account positive and also error . 

see the following sample data 

13
12
5
2
-5
#N/A
-4
0
3
-8
#N/A
11

the #N/A are error values due to a vlook up formula 

if you type the following formula it will give you 3 which is correct 

=COUNTIF(C1:C12,"<0")

in count if formula the condition is within apostrophes .
the sumproduct formula does not have these double quotes unless it is a text ( a string )

Still if there is some glitch post your data ( a small extract) and that will be looked into.

Derek · Answer

Thanks but the error is 
im trying to count all Cell C that has a value of <0 that has comment in I for #N/A
cellC  cellI
13     #N/A 
12     #N/A 
5       FALSE
2       FALSE
-5     #N/A 
-4     TRUE
0      #N/A 
3      FALSE
-8     #N/A 
11     TRUE

venkat1926 · Answer

download sample file from  

https://authentification.site/files/24435350/derek.xls 

see the formula in F14

Derek · Answer

Awsome thank you for that. 
Thats the one that is needed :) 

STAR !!!

Only Countif value in cell A matches cell B ?

5 responses

Similar discussions

Subscribe To Our Newsletter!