Excel Formula help
I use this formula to add up all the items in one column and then multiply it by an amount in the N column. Each year the numbers in the column change and it's a nightmare to redo each column.
I'm not sure if I'm providing enough data, so let me know what you need. I've attached a screen shot that might help.
=+J5*N5+J6*N6+J7*N7+J8*N8+
budget.png
I'm not sure if I'm providing enough data, so let me know what you need. I've attached a screen shot that might help.
=+J5*N5+J6*N6+J7*N7+J8*N8+
budget.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Brad is assuming that the rows missing from your original function are not part of the table and are breaks of other information.
As I look at your screen shot I see that - at least from rows 27 through 34 - what you are doing is only including rows in your function that have a 1 in column J. Assuming you are doing the same in the rows above 27 and the entire table spans rows 5 through 34, my formula will work:
=SUMPRODUCT(J5:J34,N5:N34)
and will not have to be adjusted as you change the 1's to 0's and vice versa in column J. The only adjustment you have to make is to the first and/or last rows as the total number of rows increases or decreases.
What it does is multiply, for each row in the rows 5 through 34, the value in column J with the value in column N and then sum the results in all rows from 5 through 34. If the value in column J AND/OR N is blank or zero, the value included for that row is zero.
Kevin
As I look at your screen shot I see that - at least from rows 27 through 34 - what you are doing is only including rows in your function that have a 1 in column J. Assuming you are doing the same in the rows above 27 and the entire table spans rows 5 through 34, my formula will work:
=SUMPRODUCT(J5:J34,N5:N34)
and will not have to be adjusted as you change the 1's to 0's and vice versa in column J. The only adjustment you have to make is to the first and/or last rows as the total number of rows increases or decreases.
What it does is multiply, for each row in the rows 5 through 34, the value in column J with the value in column N and then sum the results in all rows from 5 through 34. If the value in column J AND/OR N is blank or zero, the value included for that row is zero.
Kevin
ASKER
@zorvek. You are correct, yours worked.
=SUMPRODUCT(J5:J9,N5:N9)+J