I used SUMPRODUCT formula to retrieve value from column C2:E4 which contains a number and a text value, but I want to ignore the text
Example of my desired result:
A B C D E F G H
NO. NAME YES NO YES YES 80
1. Dave 10 20 x
2. Sue x 10 30
3. Hans 10 y 30
In H2, this formula works when there is no Text cells:
=SUMPRODUCT(($C$1:$E$1=G1)*($C$2:$E$4))
In this code I have tried to use ISNUMBER to ignore text (x and y), but it doesn't work. I have already tried several ways to make it work but it doesn't.
Please help.
pertaining to the use of SUMPRODUCT and ISNUMBER formulae, this is what i got after testing.
=SUMPRODUCT(($C$1:$E$1=G1)*--ISNUMBER($C$2:$E$4),($C$2:$E$4))
the double negative helps to ignore the text values (x and y), while adding an additional array helps to compute the sum correctly (without the array, the output gives 4).
hope this helps!