Countifs and ranges

Question

I'm trying to write some code that requires a cell value to be within a range of dates. This is what I have written so far (albeit a few name changes for simplicity):

=IF(COUNTIFS('[SheetA.xlsx]TabA'!A2:AA2, "submit", '[SheetA.xlsx]TabA'!B2:AB2, '[SheetA.xlsx]TabB'!**A9:A13**) >= 1, '[SheetA.xlsx]TabA'!C2, "")

Basically, if a cell in a row contains the word "submit" AND the cell to the right of it has a date (within a specific range of five days), I'd like the function to return the third cell of that row.

The range in bold is a range of dates.

This formula doesn't work when I use a range, but returns expected values when I enter a single date. What should I change?

Answer 1

As for COUNTIF() not finding a date in a range, if the range is consecutive, you can get around that like so:

COUNTIFS(A2:AA2, "submit", B2:AB2,">=" & LowestDate,B2:AB2,"<=" & HighestDate)

The problem there is that you're comparing a range to a range, so the whole range has to match the whole other range. This will always return false since the ranges are not even the same shape. When instead you compare a range to a single value, Excel automatically assumes you want true if the single value exists anywhere in the range.

It sounds like your other issue is that since COUNTIFS() does not return where it found "submit" and a date, the outer IF statement cannot determine where the third cell is.

If there's only going to be one submit, you could use Match("submit",2:2,0)+2 to find the relative column of "submit" add 2 to it, and then use ADDRESS() to build the address of the third cell based on the row. Last you put that inside of an INDIRECT() to return the third cell. Something like:

=INDIRECT(ADDRESS(ROW(A2),MATCH("submit",2:2,0)+2,1,1))

Note that this will not work for multiple matches as you're returning the first value MATCH() finds.

I use ROW() because ADDRESS() may not fill the row series very well when you drag the formula down a column if you use a plain row number.