Hi Anil,

Welcome to our forum! Please upload an Excel file containing your data and desired result and explain in words what you're after as I'm not sure what the above is asking.

Many thanks,

Mynda

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. 

Hi Anil,

Try: =SUMPRODUCT(IFERROR((C12:I13)/ (B12:H13="x"),0))

=SUMPRODUCT(IFERROR((C12:I13)/ (B12:H13="y"),0))

Simply, Marvelous. Working perfectly. I entered the formula as "Array Formula" by "CTRL+SHIFT"..ENTER.

Working. But giving 0 if we do not enter as array formula.

Thanks, Really obliged ...

Further, you are kindly requested to explain, How this overall formula works please.

That will be a great help !

thanks & great regards.

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.