Log In
Register
Home
See Answer
July 16, 2010
Offline
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
Active Member
September 18, 2019
Offline
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
Offline
Hi Anil,
Try: =SUMPRODUCT(IFERROR((C12:I13)/ (B12:H13="x"),0))
=SUMPRODUCT(IFERROR((C12:I13)/ (B12:H13="y"),0))
Answers Post
Active Member
September 18, 2019
Offline
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 ...
Active Member
September 18, 2019
Offline
Further, you are kindly requested to explain, How this overall formula works please.
That will be a great help !
thanks & great regards.
November 8, 2013
Offline
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)
