Aug 12 2022 05:45 AM
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.
Aug 12 2022 06:02 AM
Solution=SUMPRODUCT(($C$1:$E$1=$C13)*($C$2:$E$10=D$12))
You can try this formula for the data layout of the example.
Aug 12 2022 06:30 AM
Aug 12 2022 06:34 AM
Aug 12 2022 07:22 AM
$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.
Aug 12 2022 06:02 AM
Solution=SUMPRODUCT(($C$1:$E$1=$C13)*($C$2:$E$10=D$12))
You can try this formula for the data layout of the example.