Pivot Table Formula-- COUNTIF?

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?

You have to change to sumproduct, and it does not accept a column, but will accept a1:a65535 instead which is probably more than enough.

=IF(OR(H5<>"WEB",AND(J5<>"",TODAY()>J5)),0,1/sumproduct(($A$1:$A$100=A5)*($j$1:$j$100>=today())

Regards

Dav

Thank worked as long as there were no blanks.

I changed your formula to:

=IF(OR(H2<>"WEB",AND(J2<>"",TODAY()>J2)),0,1/SUMPRODUCT(($A$1:$A$100=A2)*OR(($J$1:$J$100>=TODAY()),($J$1:$J$100=""))))

because a cell should be counted if J is blank, or if J is greater than TODAY(), but this didn't return the correct result (it actually went back to counting all of A1:A100 that equalled A2)

I would create the last column with the formula: =IF(J2="";INACTIVE;IF(J2<>"",TODAY()>J2;ACTIVE;INACTIVE))

Then I would create a Pivot that either filters data with a droplist for ACTIVE/INACTIVE in the Page Field section. Then I would add the Names in the row fields.
then add the DATA summary you want in the DATA field.