[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

sum product help

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
Frank Freese
Asked:
Frank Freese
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Rory ArchibaldCommented:
I have no clue from those ranges what you are trying to do - could you post a small sample?
0
 
Ardhendu SarangiSr. Project ManagerCommented:
Hi

Can you try this -


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

thanks
Ardhendu
0
 
barry houdiniCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
barry houdiniCommented:
sorry, missing parethesis, change to

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

0
 
Frank FreeseAuthor Commented:
sure - it is attached
sumproduct.xlsx
0
 
barry houdiniCommented:
For that example you can use SUMIF, i.e.

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

regards, barry
0
 
barry houdiniCommented:
Oops! you're summing column G aren't you? so that should be

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

barry
0
 
Ardhendu SarangiSr. Project ManagerCommented:
Try this in your totals column - =SUMPRODUCT((G3:G42-H3:H42=0)*(H3:H42))

See attached,
sumproduct.xlsx
0
 
Frank FreeseAuthor Commented:
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
 
Frank FreeseAuthor Commented:
I was able to modify what you sent to work - thank you
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now