COUNTIF error

[shellataylor]

All,

I'm trying to get the input from this table

Screen Shot 2016-02-04 at 9.16.41 AM.png

to populate into this table if the "Date Out" (column 0) <= TODAY()+10. I'd like it to populate from earliest Date Out to latest.

Screen Shot 2016-02-04 at 9.16.49 AM.png

I'm using this formula, but it's returning the error "you've entered too few arguments..." Can someone please advise?

=IF(ROWS($A$1:$A1)>COUNTIF($O$2:$O$1000<=TODAY()+10,"",INDEX($N$2:$N$1000,SMALL(INDEX(($O$2:$O$1000<=TODAY()+10)*(ROW($A$2:$A$1000)-ROW($A$2)+1),),COUNTA($N$2:$N$1000)+COUNTBLANK($N$2:$N$1000)-COUNTIF($O$2:$O$1000<=TODAY()+10+ROWS($A$1:$A2))))

Thank you!!!

[JohnTopley]

Please post a sample Excel file rather than images.

[Jonmo1]

Both occurances of
COUNTIF($O$2:$O$1000<=TODAY()+10

need to be changed to
COUNTIF($O$2:$O$1000,"<="&TODAY()+10)

[shellataylor]

Jonmo,

I tried your suggestion, but am still having no luck. Also, per JohnTopley's suggestion, I've attached an excel file.

The formula isn't pasted into Column V yet, as excel won't accept it as a formula. Here's where I left off:

=IF(ROWS($A$1:$A1)>COUNTIF($O$2:$O$1000"<="TODAY()+10,"",INDEX($N$2:$N$1000,SMALL(INDEX(($O$2:$O$1000<=TODAY()+10)*(ROW($A$2:$A$1000)-ROW($A$2)+1),),COUNTA($N$2:$N$1000)+COUNTBLANK($N$2:$N$1000)-COUNTIF($O$2:$O$1000"<="TODAY()+10+ROWS($A$1:$A2))))

PTO_test.xlsx


Thank you!!

[Jonmo1]

Jonmo,

I tried your suggestion

Not quite. Your countif functions are still missing elements that I suggested.

Both occurances of
COUNTIF($O$2:$O$1000<=TODAY()+10

need to be changed to
COUNTIF($O$2:$O$1000,"<="&TODAY()+10)

[Special-K]

Try

in U2
=IFERROR(INDEX($N$2:$R$5,SMALL(IF($O$2:$O$5 < TODAY()+10,ROW(INDIRECT("1:"&ROWS($N$2:$N$5)))),ROW(N1)),COLUMN()-20),"")}
Array formula, use Ctrl-Shift-Enter

copy across to Y2
then copy U2:Y2 down the columns

[shellataylor]

Jonmo1 - Good point, I missed that. I added in the , & and ) as suggested. Now the formula returns nothing/blank cell. I've attached for your consideration.

PTO_test.xlsx


Special-K, I pasted your formula in and tried to apply the array...it doesn't seem to like the formula. I likely did something wrong

Thanks!!

[Special-K]

You'll need to take out the spaces around the < , inserted due to the forum corrupting posts with these symbols.

I didnt test it, it may be my fault.

Replace COLUMN()-20 with 1 and as you copy it along the row increase that number each time till you get 5 for column R

[JohnTopley]

Special-K already replied!!

[shellataylor]

Special K - I've removed the spaces and changed COLUMN 20 to 1 (both replacing only the number and replacing the entire "COLUMN()-20" with 1, as I wasn't sure which you intended. It didn't like either one of the formulas....so I still must be doing something wrong. Thoughts?

=IFERROR(INDEX($N$2:$R$5,SMALL(IF($O$2:$O$5<TODAY()+10,ROW(INDIRECT("1:"&ROWS($N$2:$N$5)))),ROW(N1)),COLUMN()-1),"")}

=IFERROR(INDEX($N$2:$R$5,SMALL(IF($O$2:$O$5<TODAY()+10,ROW(INDIRECT("1:"&ROWS($N$2:$N$5)))),ROW(N1)),1,"")}

[JohnTopley]

In U2

=IFERROR(INDEX($N$2:$R$6,SMALL(IF($O$2:$O$6 < TODAY()+10,ROW(INDIRECT("1:"&ROWS($N$2:$N$6)))),ROW(N1)),COLUMNS($A:A)),"")

Enter with Ctrl+Shift+Enter

Copy across

[shellataylor]

This worked brilliantly - thank you!

[JohnTopley]

Thanks are due to Special-K - it's his formula!