# Thread: Loop Through Visible Columns within Range Thanks:  2 Post #5345278 (1)Post #5345592 (1) Likes: 0

1. ## Re: Loop Through Visible Columns within Range

Something like

=COUNTIF(E2:E599,"<="&(TODAY()+30))

might do it, I'm not sure that I follow the purpose of your existing formula.

2. ## Re: Loop Through Visible Columns within Range

It would be go much easier to PM the spreadsheet to you in order to explain as I'm unable to post a picture URL etc.
To further complicate matters, there are some courses that don't have an expiry date so they will be picked up by the CountIf formula as well.

3. ## Re: Loop Through Visible Columns within Range

You could upload a sample workbook to a site such as dropbox.com then put the link here.

4. ## Re: Loop Through Visible Columns within Range

Originally Posted by aoifew
To further complicate matters, there are some courses that don't have an expiry date so they will be picked up by the CountIf formula as well.
That is another common misconception, there is usually a way to ignore 0 values (i.e. those with no expiry date).

A sample uploaded to a file share site as Akuini suggested would help significantly.

5. ## Re: Loop Through Visible Columns within Range

Good thinking Akuini. Hope this works

6. ## Re: Loop Through Visible Columns within Range

See if this does what you need, using your sample file,

D13, array confirmed with Ctrl Shift Enter (see https://exceljet.net/glossary/cse )

=SUM(IFERROR((D6:D10<(TODAY()-\$C\$6:\$C\$10))*(D6:D10<>""),0))

D14, array confirmed with Ctrl Shift Enter

=SUM(IFERROR((D6:D10<(TODAY()+30-\$C\$6:\$C\$10))*(D6:D10<>""),0))-D13

D15

=COUNT(D6:D10)-SUM(D13:D14)

D16

=COUNTIF(D6:D10,"=")

Once entered, they can all be dragged right as needed to fill the other columns.

7. ## Re: Loop Through Visible Columns within Range

Thanks so much Jason, I'll give this a go tomorrow

8. ## Re: Loop Through Visible Columns within Range

Awesome, absolutely awesome!!! Can't thank you enough Jason