Forum Discussion
Help needed with excel formula
Hello, I'm needing to write a single array formula in a cell that sums the value of [high (named range1)* Low (named range2)] for all locations that have a value less than 10c.
A B
1 High Low
2 27 15
3 20 11
4 17 7
5 15 7
6 26 16
I've been trying variations of =SUM(High_C*Low_C,IF(Low_C<10,0)) but it's not working out. Any advice?
Hi Michael,
Please try this formula and find it in the attached file:
=SUMPRODUCT(--(B2:B6<10),A2:A6,B2:B6)
Hope that helps
Haytham
Thank you so much, I forgot about adding two -- sign's. I'll google what they do so I can learn from this experience. Are they wildcard functions... No that's just * ~ symbols.... If I can ask a follow up, what do the two -- do to my formula?
Thanks in advance, I really appreciate it.Hi Michael,
The double negative sign is used to convert the array of TRUEs and FALSEs to 1s and 0s so that the SUMPRODUCT function can read them in order to filter the array from the values that are greater than 10 to give you the desired result:
--(B2:B6<10)
--({FALSE;FALSE;TRUE;TRUE;FALSE}){0;0;1;1;0}
