Related:
- Only Countif value in cell A matches cell B ?
- Clear only the formatting from the selected cell (leaving the content) - Guide
- Conditional formatting if cell contains text - Excel Forum
- Count if cell contains number - Excel Forum
- If cell contains date then return value - Excel Forum
- If cell A1 has text then cell B2 has today's Date ✓ - Excel Forum
5 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 27, 2010 at 09:54 AM
Sep 27, 2010 at 09:54 AM
download sample file from
https://authentification.site/files/24435350/derek.xls
see the formula in F14
https://authentification.site/files/24435350/derek.xls
see the formula in F14
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 23, 2010 at 11:17 PM
Sep 23, 2010 at 11:17 PM
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
=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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 24, 2010 at 07:47 AM
Sep 24, 2010 at 07:47 AM
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.
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
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
Didn't find the answer you are looking for?
Ask a question
