Greetings all, having another issue.

First, let me preface this by saying that I'm not very familiar with pivot tables, but I suspect this actually applies to any general formula as well.

The sheet has a series of columns tracking the time a person spends on a particular project. Column A contains the person's name (in no particular order), column B contains the splitcount (more on that later), and column J contains the last date the person will work on a project. This sheet keeps historical project data, so data for projects that have concluded are still retained (therefore, their column J would be less than TODAY())

My question regards the splitcount (column B), which should be 1 divided by the number of active projects if the project on that row is active (TODAY() < column J), and 0 if the project is not active (TODAY() > column J.)

My formula at the moment is (for cell B5):

=IF(OR(H5<>"WEB",AND(J5<>"",TODAY()>J5)),0,1/COUNTIF(A:A,A5))

So far, I have gotten the conditions working to return 0 for an inactive project. The conditional statement before COUNTIF is correct, so it can be ignored.

Currently, the COUNTIF is counting (correctly) the total number of projects that the person in A5 has ever worked on. I need to add a condition that will then check the J column for each project to make sure it is active: OR(J5 = "", TODAY() < J5)

I have tried making the COUNTIF as follows:
COUNTIF(A:A,AND(A5,OR(J:J = "", TODAY() < J:J))

Neither confirming it normally (enter) or as an array (shift+ctrl+enter) has worked so far.

Any ideas?