
Active Member

September 18, 2019

In the attached sample file, x & y being variables are distributed in the table. The No written in front of variable is the value of that variable. USING excel functions , I want to sum up all the values for x and all the values for y. Example for all the Xs the sum is 13 and all the Ys sum up to 5 in the attached file.


November 8, 2013

B12:H13="y" will produce an array of True and False values, there will be one true/false value for each cell in the range.
If you divide the next cell by the true/false values: (C12:I13)/ (B12:H13="y"), the ones without a "y" will produce an error, because dividing by a False value is equal to dividing a number by zero. Same with the values tat passes the evaluation: dividing a cell by True is equal to dividing by 1, the current values from C11:I13 will remain unchanged after division. Iferror will just replace errors with zero, to allow SUMPRODUCT to finish adding the values that passed the evaluation.
1 Guest(s)
