COUNT IFS NON BLANK (but formula in cell make it count as non blank even if blank)

G

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
119
Hi guys,

I'm trying to figure out how to do a count if where both columns aren't blank but the problem is that I have the following formula and it counts them as non-blank either way even if they are blank.

Formula in cells is =IFERROR(IF($O3<$O2,$J3-K3,NA()),"") so that if there's an error, it shows as blank "". But when I try to do count ifs. It doesnt work. What is faulty in my syntax?

I've tried this:
1) =COUNTIFS(A3:A87,"<>",C3:C87,"<>") = That counts all 85 rows even if blank.
2) =COUNTIF(E3:E87,AND(A3:A87<>"",C3:C87<>"")) = 0 (E column, I just put random number so that it would count them if the condition is met), doesn't work.

What should I use? To count how many times there are both flattening (A) + tightening (C) environments, A + D, B + C, B + D. Thank you!

[TABLE="width: 340"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Value-added during flattening (A)[/TD]
[TD]Value-added during steepening (B)[/TD]
[TD]Value-added during tightning credit spreads (C)[/TD]
[TD]Value-added during widening credit spreads (D)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.33%[/TD]
[TD] [/TD]
[TD="align: right"]0.33%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.12%[/TD]
[TD] [/TD]
[TD="align: right"]-0.12%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.29%[/TD]
[TD] [/TD]
[TD="align: right"]0.29%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.02%[/TD]
[TD] [/TD]
[TD="align: right"]-0.02%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-0.07%[/TD]
[TD] [/TD]
[TD="align: right"]-0.07%[/TD]
[/TR]
[TR]
[TD="align: right"]0.30%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.30%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.24%[/TD]
[TD] [/TD]
[TD="align: right"]-0.24%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-0.33%[/TD]
[TD] [/TD]
[TD="align: right"]-0.33%[/TD]
[/TR]
[TR]
[TD="align: right"]-1.22%[/TD]
[TD] [/TD]
[TD="align: right"]-1.22%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.90%[/TD]
[TD="align: right"]0.90%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.35%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.35%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.27%[/TD]
[TD] [/TD]
[TD="align: right"]-0.27%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.27%[/TD]
[TD] [/TD]
[TD="align: right"]0.27%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.55%[/TD]
[TD] [/TD]
[TD="align: right"]0.55%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.54%[/TD]
[TD="align: right"]0.54%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.32%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.32%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.72%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.72%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.49%[/TD]
[TD="align: right"]0.49%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.52%[/TD]
[TD] [/TD]
[TD="align: right"]0.52%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.29%[/TD]
[TD="align: right"]0.29%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.13%[/TD]
[TD="align: right"]0.13%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.28%[/TD]
[TD] [/TD]
[TD="align: right"]0.28%[/TD]
[/TR]
[TR]
[TD="align: right"]0.19%[/TD]
[TD] [/TD]
[TD="align: right"]0.19%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.46%[/TD]
[TD="align: right"]0.46%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.09%[/TD]
[TD="align: right"]0.09%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-0.40%[/TD]
[TD="align: right"]-0.40%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.06%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.06%[/TD]
[/TR]
[TR]
[TD="align: right"]0.39%[/TD]
[TD] [/TD]
[TD="align: right"]0.39%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.04%[/TD]
[TD] [/TD]
[TD="align: right"]0.04%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-0.03%[/TD]
[TD] [/TD]
[TD="align: right"]-0.03%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.07%[/TD]
[TD="align: right"]0.07%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-0.14%[/TD]
[TD] [/TD]
[TD="align: right"]-0.14%[/TD]
[/TR]
[TR]
[TD="align: right"]0.38%[/TD]
[TD] [/TD]
[TD="align: right"]0.38%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.30%[/TD]
[TD] [/TD]
[TD="align: right"]0.30%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-0.06%[/TD]
[TD="align: right"]-0.06%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.35%[/TD]
[TD="align: right"]0.35%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.72%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.72%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.61%[/TD]
[TD="align: right"]0.61%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.77%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.77%[/TD]
[/TR]
[TR]
[TD="align: right"]0.24%[/TD]
[TD] [/TD]
[TD="align: right"]0.24%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.19%[/TD]
[TD] [/TD]
[TD="align: right"]0.19%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.25%[/TD]
[TD] [/TD]
[TD="align: right"]0.25%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.10%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.10%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.01%[/TD]
[TD] [/TD]
[TD="align: right"]-0.01%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.01%[/TD]
[TD] [/TD]
[TD="align: right"]0.01%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.16%[/TD]
[TD="align: right"]0.16%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.47%[/TD]
[TD] [/TD]
[TD="align: right"]-0.47%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.36%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.36%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-1.31%[/TD]
[TD] [/TD]
[TD="align: right"]-1.31%[/TD]
[/TR]
[TR]
[TD="align: right"]0.40%[/TD]
[TD] [/TD]
[TD="align: right"]0.40%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.29%[/TD]
[TD] [/TD]
[TD="align: right"]0.29%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.70%[/TD]
[TD="align: right"]0.70%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]-0.07%[/TD]
[TD] [/TD]
[TD="align: right"]-0.07%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.11%[/TD]
[TD] [/TD]
[TD="align: right"]0.11%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.23%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.23%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.38%[/TD]
[TD] [/TD]
[TD="align: right"]0.38%[/TD]
[/TR]
[TR]
[TD="align: right"]0.35%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.35%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]0.50%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.19%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.19%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.56%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.56%[/TD]
[/TR]
[TR]
[TD="align: right"]0.10%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.10%[/TD]
[/TR]
[TR]
[TD="align: right"]0.27%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.27%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.12%[/TD]
[TD="align: right"]0.12%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.30%[/TD]
[TD="align: right"]0.30%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.19%[/TD]
[TD] [/TD]
[TD="align: right"]-0.19%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.25%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.25%[/TD]
[/TR]
[TR]
[TD="align: right"]0.06%[/TD]
[TD] [/TD]
[TD="align: right"]0.06%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.25%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.25%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.13%[/TD]
[TD] [/TD]
[TD="align: right"]0.13%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.21%[/TD]
[TD] [/TD]
[TD="align: right"]0.21%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.30%[/TD]
[TD="align: right"]0.30%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.71%[/TD]
[TD="align: right"]0.71%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.42%[/TD]
[TD="align: right"]0.42%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.02%[/TD]
[TD] [/TD]
[TD="align: right"]0.02%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.13%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.13%[/TD]
[/TR]
[TR]
[TD="align: right"]-0.08%[/TD]
[TD] [/TD]
[TD="align: right"]-0.08%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.13%[/TD]
[TD] [/TD]
[TD="align: right"]-0.13%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.32%[/TD]
[TD] [/TD]
[TD="align: right"]0.32%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]0.50%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.15%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.15%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1.16%[/TD]
[TD="align: right"]1.16%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.25%[/TD]
[TD] [/TD]
[TD="align: right"]-0.25%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.21%[/TD]
[TD] [/TD]
[TD="align: right"]-0.21%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.13%[/TD]
[TD] [/TD]
[TD="align: right"]0.13%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.73%[/TD]
[TD] [/TD]
[TD="align: right"]0.73%[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Assuming the numbers are all percentages (all less than 1)
Try

=COUNTIFS(A3:A87,"<=1",C3:C87,"<=1")



Edit:
I see there are some 1+ percentages. Just need to use a large enough number to accommodate all possible numeric values.
Standard Bignum is 9.99999999999999E+307, so

=COUNTIFS(A3:A87,"<=9.99999999999999E+307",C3:C87,"<=9.99999999999999E+307")
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
If Formula
Replies
3
Views
254
Joe4
Joe4
G
  • Question Question
Complex problem - how many widgets should I sell?
Replies
9
Views
641
Dan_W
D
1
  • Question Question
Calculate on every third Friday of the month and show results between positive & negative weeks
2
Replies
14
Views
958
15minoffame
1
R
  • Question Question
Having Trouble Finding Values
Replies
5
Views
341
Fluff
Fluff
R
  • Solved
Very Complex- How to Find Stock Market Probability of 3, 4 & 5 negative days (Find Results of Day 4,5&6)
Replies
8
Views
605
rebel123
R

Forum statistics

Threads
1,223,954
Messages
6,175,600
Members
452,658
Latest member
GStorm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top