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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
251
Joe4
Joe4
G
  • Question Question
Complex problem - how many widgets should I sell?
Replies
9
Views
636
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
949
15minoffame
1
R
  • Question Question
Having Trouble Finding Values
Replies
5
Views
339
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
601
rebel123
R

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

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