Excel 2013: I want a cell to be blank or have a value based on data/no data in a specific cell and data/no data in a range of cells.
So in cell AJ7 I want either a numerical value or no value.
Cell B7 has either a name or no name (however the cell has a formula associated with it). It gets populated with Names from another sheet in the workbook.
Cells C7:AG7 either have data (A, N, L, V, or H) or no data.
If Cell B7 has data and Cells C7:AG7 have no data: Cell AJ7 needs to have a numerical value of (.5).
If Cell B7 has data and Cells C7:AG7 have data: Cell AJ7 needs to have a value of (0).
If Cell B7 has no data and Cells C7:AG7 have no data: Cell AJ7 needs to have no value.
-----------------------------------------------------------------------------------------------------------------I can get a value in AJ7 if I use COUNTA for the range of Cells A7:AG7 but if I add a variable for Cell B7 (or Sheet2!B7) I generally get notified that the formula is not a formula. I have tried to add "AND" but no work.
-----------------------------------------------------------------------------------------------------------------Formulas that produce a value in AJ(x), but do not include the secondary variable --
=IF(COUNTA(C7:AG7)=0, ".5","0")
=IF(COUNTA('Employee Names'!B7)=0,"0",".5")
-----------------------------------------------------------------------------------------------------------------Formula with "AND" that does not work: (you've entered too many arguments)
=IF(AND(COUNTA('Employee Names'!B7="1")),C7:AG7,"0",".5")
