SOLVED

IF formula to calculate outcome

Copper Contributor

jojo29_0-1660308088340.png

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

@jojo29 

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

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

patience.JPG 

@OliverScheurich thank you so much. I knew someone would have an answer ASAP. Have a good day
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?

@jojo29 

$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.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@jojo29 

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

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

patience.JPG 

View solution in original post