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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
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)