Solved

sum product help

Posted on 2011-03-09
10
337 Views
Last Modified: 2012-06-21
Experts,
In my spreadsheet, at R43:H43 I need to evaluate using sumproduct as follows:

Sum G4:G42 only if each corresponding formula such as F4-G4 = 0.

For example, if F16-G16 <> 0 do not include the value of G16 in my sum in R43:H43
0
Comment
Question by:Frank Freese
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35084566
I have no clue from those ranges what you are trying to do - could you post a small sample?
0
 
LVL 20

Expert Comment

by:pari123
ID: 35084580
Hi

Can you try this -


=SUMPRODUCT((F2:F42-G2:G42=0)*(G2:G42))

thanks
Ardhendu
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35084593
I'm not sure why you want this in a range, how is H43 different from I43 or J43?

For what you asked specifically try

=SUMPRODUCT((F4:F42=G4:G42+0,G4:G42)

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35084601
sorry, missing parethesis, change to

=SUMPRODUCT((F4:F42=G4:G42)+0,G4:G42)

0
 

Author Comment

by:Frank Freese
ID: 35084674
sure - it is attached
sumproduct.xlsx
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 50

Expert Comment

by:barry houdini
ID: 35084715
For that example you can use SUMIF, i.e.

=SUMIF(I3:I6,0,H3:H6)

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35084731
Oops! you're summing column G aren't you? so that should be

=SUMIF(I3:I6,0,G3:G6)

barry
0
 
LVL 20

Accepted Solution

by:
pari123 earned 500 total points
ID: 35084733
Try this in your totals column - =SUMPRODUCT((G3:G42-H3:H42=0)*(H3:H42))

See attached,
sumproduct.xlsx
0
 

Author Comment

by:Frank Freese
ID: 35084949
the user just changed their mind (surprised?)
here's what I have that is not working
=SUMPRODUCT((F4:F42=H4:H42),H4:H42)

if the value in column H = the value in column F add the value in Column H

for example, if in H5 the value is $800 and in F5 the value is $801 do not sum.
if in H6 the value = $800 and in F6 the value is $800 sum
0
 

Author Closing Comment

by:Frank Freese
ID: 35085245
I was able to modify what you sent to work - thank you
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now