perform calc based on if other cells are populated

[osaben]

I am wondering if anyone has any suggestions to assist me with my problem. Below is a sample of what my data looks like.



A B C D E F G H I J
62 Shop 456.20 NO YES NA NA NA Overtime
63 Check 8756.00 YES NA NA NA NA Sheet2 $B$5 data
64
65 Turn 485.00 PointInTime
66 Drive 41.25 NO NO YES NA NA Overtime
67 Return 697.10 NO NO NO YES NO PointInTime



Background: I am looking to have a series of 5 questions (column C, D, F, H & I) answered in sequential order with each available answer being dependent on the previous answer. I seem to have that working (via dependent dropdowns). I then want to use the answers to those 5 questions to populate Column J.


Here is the formula I have in Column J:

=IF(COUNTA(C62:I62)=0,"",IF(A62="","",IF((C62="Yes"),'IP Licensing'!$K$29,IF(OR(D62="YES",F62="YES"),"Overtime ",IF(AND(H62="YES",I62="YES"),"Overtime","PointInTime")))))

The problem I am having is If there is data in column A, it is populating Column J even though no questions have been answered (see row 65 above). What I really need is for Column J only to be populated when Column A has data in it AND the 5 question have been answered.

Can anyone help? Please & Thank You!

 

[Aryatect]

Hi,


I think there can be atlteast 3 ways:


1. This would be individually checking each cell for blanks and Column J would have:

Book1
	J
2	Your Formula
Sheet5

Cell Formulas
Range	Formula
J2	=IF(AND(A2<>"",C2<>"",D2<>"",F2<>"",H2<>"",I2<>""),"Your Formula","")

2. Using an array formula in Column J:

Book1
	J
2	Your Formula
Sheet5

Cell Formulas
Range	Formula
J2	{=IF(SUMPRODUCT(IF(IF(A2:I2<>"",COLUMN(A1:I1))*{1,0,3,4,0,6,0,8,9},1))=6,"Your Formula","")}
Press CTRL+SHIFT+ENTER to enter array formulas.

3. If you can move all the required columns together, then below in Column J:

Book1
	J
12	Your Formula
Sheet5

Cell Formulas
Range	Formula
J12	=IF(SUMPRODUCT(--(A12:F12=""))=0,"Your Formula","")

While using any of this, just replace "Your Formula" with your formula and it should work fine.

 

[Aryatect]

your table being

Book1
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F	G	H	I	J
2	62	Shop	456.2	NO	YES	NA	NA	NA	Overtime	Your Formula
3	63	Check	8756	YES	NA	NA	NA	NA	Overtime	Your Formula
4	64
5	65	Turn	485						PointInTime
6	66	Drive	41.25	NO	NO	YES	NA	NA	Overtime	Your Formula
7	67	Return	697.1	NO	NO	NO	YES	NO	PointInTime	Your Formula
Sheet5