SOLVED

New Contributor

# IF formula to calculate outcome

Can someone please assist or give suggestions

I am trying to create a formula where you can see I've highlighted (green boarder) around a cell

What I am trying to create is from the blue table above is a formula that states for example

IF Patience shows the response inherited or learned or mix of both show the result in numbers

Does anyone know how to create that formula

EXAMPLE Female Respondents

Patience shows

Inherited = 1

Learned = 4

Mix of both = 4

I want to be able to show the number 1,4,4 under each inherited, learned and mix of both as opposed to Y or N.

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: IF formula to calculate outcome

=SUMPRODUCT((\$C\$1:\$E\$1=\$C13)*(\$C\$2:\$E\$10=D\$12))

You can try this formula for the data layout of the example.

# Re: IF formula to calculate outcome

@Quadruple_Pawn thank you so much. I knew someone would have an answer ASAP. Have a good day

# Re: IF formula to calculate outcome

What do the \$ symbols represent? And also what are the numbers you have as part of the formula C1 E1 C13 and C2 E10 D12?

# Re: IF formula to calculate outcome

\$C\$1:\$E\$1 is the range from cell C1 to cell E1. The \$ signs indicate that the reference to the range is absolute, i.e. it won't change when you fill or copy the formula to other cells.

\$C13 is a reference to cell C13. Here the reference to the column letter C is absolute (fixed), but the reference to the row number 13 is relative (not absolute): it will change when you fill or copy the formula to other cells.

The expression \$C\$1:\$E\$1=\$C13 returns an array of TRUE/FALSE values: TRUE for each cell in C1:E1 that equals C13, FALSE otherwise.

Similarly, \$C\$2:\$E\$10=D\$12 returns an array of TRUE/FALSE values. \$C\$2:\$E\$10 is an absolute reference to C2:E10, while D\$12 is mixed: the column letter D is relative (changeable) and the row number 12 is absolute.