I have a data set where participants entered data into 3 databases, and I want to calculate how much of the study they completed (% completion). But it gets complicated because participants count as "completed" if they entered into dataset 1 AND dataset 2 OR 2a.
Here’s the data:
If I only needed % completion for datasets 1 & 2, it would be easy to do
=SUM(B2:J2)/9
However, what I need is something like nested SUMIF statements?
I need to know a percent completion of dataset 1 + dataset 2 or 2a.
So if a participant did not complete dataset 2 @ 4 months, but they DID complete dataset 2a @ 4months, then that counts as completion of dataset 2.
What formula can I use to calculate this?
THANK YOU!!
I tried =SUM(B2:F2) + SUMIF(G2:J2>0, K2:N2)
This doesnt work because I need each cell (G2:J2) > 0, not the total >0 or >4. I need Excel to look at each individual cell (G2:J2) and if that cell is 0, then look in cells (K2:N2)
>Solution :
Use OR to return 1 if either of the values are 1;
=(SUM(B2:F2)+IF(OR(G2=1,K2=1),1,0)+IF(OR(H2=1,L2=1),1,0)+IF(OR(I2=1,M2=1),1,0)+IF(OR(J2=1,N2=1),1,0))/9