WeThotUWasAToad
asked on
Excel formula to calculate the sum of the products of values in two different columns of qualifying rows.
Hello,
This post has two related questions:
Question #1)
What formula in Excel will display the sum of only those values in a given column which have a value (any value) in the same row of a defined adjacent column?
I am familiar with the =SUMIF() formula which returns the sum of those values in a given column which have a specified value in the same row of an adjacent column as shown in the following screenshots (Fig. 1)
However, suppose you want a similar result but instead of specifying a value in the adjacent column, you want the sum of values which have an adjacent entry regardless of its value as shown here (Fig. 2):
In this case, the formula in cell D13 should display the result 18 since that is the sum (as shown to the side) of the values in column D which have an adjacent entry in column C.
Question #2)
Now, what Excel formula in cell D13 will use the same criteria as previously (ie the presence of some entry in the same row of the adjacent column) but instead of a simple sum, will return the sum of the products of the qualifying values in column D and their corresponding values in column C (Fig. 3)?
In this case as previously, only those rows with entries in column C are included in the calculation but the desired result is the sum of the products of adjacent values in columns C & D, as shown again to the side.
Thanks
This post has two related questions:
Question #1)
What formula in Excel will display the sum of only those values in a given column which have a value (any value) in the same row of a defined adjacent column?
I am familiar with the =SUMIF() formula which returns the sum of those values in a given column which have a specified value in the same row of an adjacent column as shown in the following screenshots (Fig. 1)
However, suppose you want a similar result but instead of specifying a value in the adjacent column, you want the sum of values which have an adjacent entry regardless of its value as shown here (Fig. 2):In this case, the formula in cell D13 should display the result 18 since that is the sum (as shown to the side) of the values in column D which have an adjacent entry in column C.Question #2)
Now, what Excel formula in cell D13 will use the same criteria as previously (ie the presence of some entry in the same row of the adjacent column) but instead of a simple sum, will return the sum of the products of the qualifying values in column D and their corresponding values in column C (Fig. 3)?
In this case as previously, only those rows with entries in column C are included in the calculation but the desired result is the sum of the products of adjacent values in columns C & D, as shown again to the side.Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Missus Miss_Sellaneus
Take a look at the SUMIFS function.. it's more powerful than SUMIF. You can use SUMIFS to incorparate adjacent cells. In your second example you could use something like "=SUMIFS(....) * SUMIFS(...)", multiplying the sums of the 2 columns instead of adding the products, which will yield the same result.
here is part 2:
=SUMPRODUCT((C4:C12>0)*(C4:C12),D4:D12)
did the formulas I posted work for you?
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.