Only Countif value in cell A matches cell B ?

[Closed]
Report
-
 Derek -
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

Related:

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
download sample file from

https://authentification.site/files/24435350/derek.xls

see the formula in F14
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Thanks for that,
The part where I am stuck is the 3rd condition -
The 3rd condition must = "#N/A"

In colum C:C there are + and - amounts
I have tried =SUMPRODUCT(C:C,"<0",!I:I,"#N/A",)
so all credits in C:C that are less than 0 but equal text of #N/A are counted.

Any ideas ?
Thanks
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.
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
Awsome thank you for that.
Thats the one that is needed :)

STAR !!!