Criterion: Count only if the cell 4 rows above is blank

[johnjohn46]

Hi.

It is my first question here and I am sorry it is too trivial.

I am constructing a formula with multiple criteria.

=COUNTIFS(D21:D868, "INT OUT Employees", F21:F868, “>0.01”)

But I have one more criterion. It is that I do not want to count a cell if the cell 4 rows above is not empty. How do I add such a criterion?

ADDRESS or ROW-commands??Hi.

 

[Teeroy]

Hi and welcome to the forum.

I'm not sure this can be done as a single formula (but there a couple of members here who can do the seemingly impossible with array formulas). The easiest way is to create a helper column. I'm going to assume the helper is column G, and the column that you want to check for a blank is column D (it's not specified).

in g21: =ISBLANK(D17)

and fill down to G868.

Then change your countifs formula to:

=COUNTIFS(D21:D868, "INT OUT Employees", F21:F868, ">0.01",G21:G868,FALSE)

 

[johnjohn46]

Thank you very much, Teeroy.
I like the professional and friendly tone in here

Thank you for your reply. Your way can solve the problem. It just add 140 new columns to the spreadsheet, which should be user friendly for my coworkers. But of cause I could just hide them.

That's my I hope that there was some kind of shortcut, which could be used.

My SUMPRODUCT-qualifications cannot handle this issue, since there is more reference cells with the same name (sorry for my unprofessional language )

Hi and welcome to the forum.

I'm not sure this can be done as a single formula (but there a couple of members here who can do the seemingly impossible with array formulas). The easiest way is to create a helper column. I'm going to assume the helper is column G, and the column that you want to check for a blank is column D (it's not specified).

in g21: =ISBLANK(D17)

and fill down to G868.

Then change your countifs formula to:

=COUNTIFS(D21:D868, "INT OUT Employees", F21:F868, ">0.01",G21:G868,FALSE)

 

[Teeroy]

You don't necessarily need to add 140 columns if it's the same test to be carried out.
You could use an And statement, or a multiplication, or countblanks (or other methods) to test whether 4 cells above in 140 places are blank and return that result to one helper column, which you then hide (as you've already considered ).

e.g.
in g21: =COUNTBLANK(H17:ER17)
and fill down.

Then change the countifs formula to:

=COUNTIFS(D21:D868, "INT OUT Employees", F21:F868, ">0.01",G21:G868,"=0")