Link to home
Create AccountLog in
Avatar of J.R. Sitman
J.R. SitmanFlag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Unless column J has a lot of fortuitous zeros, the SUMPRODUCT over the entire range will give a different answer from the original formula or SUMPRODUCT over the actual range:
=SUMPRODUCT(J5:J9,N5:N9)+J15*N15+J17*N17+SUMPRODUCT(J21:J25,N21:N25)+J29*N29+J31*N31+J34*N34
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
Avatar of J.R. Sitman

ASKER

@zorvek.  You are correct, yours worked.