J.R. Sitman

asked on

# 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+J9*N9+J15*N15+J17*N17+J21*N21+J22*N22+J23*N23+J24*N24+J25*N25+J29*N29+J31*N31+J34*N34

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

Create an account to see this answer

Signing up is free. No credit card required.

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