Excel sum if then?

%3CLINGO-SUB%20id%3D%22lingo-sub-2753545%22%20slang%3D%22en-US%22%3EExcel%20sum%20if%20then%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2753545%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20to%20do%20a%20sum%20of%20the%20answers%20in%20Col%20M%20without%20using%20Col%20M.%26nbsp%3B%20Do-able%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tami_winkler_0-1631723149136.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310621i5E7B2B2517B37E9D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tami_winkler_0-1631723149136.png%22%20alt%3D%22tami_winkler_0-1631723149136.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2753545%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2753927%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sum%20if%20then%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2753927%22%20slang%3D%22en-US%22%3ESomething%20like%20this%20should%20work%3A%3CBR%20%2F%3E%3DSUMPRODUCT(IF(H%3AH%2BK%3AK%26lt%3B%3D0%2CH%3AH%2C-K%3AK))%3C%2FLINGO-BODY%3E
New Contributor

I am looking to do a sum of the answers in Col M without using Col M.  Do-able?

tami_winkler_0-1631723149136.png

 

5 Replies
Something like this should work:
=SUMPRODUCT(IF(H:H+K:K<=0,H:H,-K:K))

@mtarler   When I use the columns, it works.  However, when I use only the rows I need, it brings back #VALUE!

maybe you could share the actual formula or better yet the worksheet (no confidential info). You need to make sure if you ref only a set of rows that all references include the same number of rows

@mtarler   Attached file - thanks!

Why are there '@' symbols in there?
=SUMPRODUCT(IF(@A12:A21+@B12:B21<=0,A12:A21,-@B12:B21))
Got rid of those '@' symbols and it worked fine.
the '@' symbol is used to force only 1 item to return so for example in a table you might want only use the cell in the same row as the formula so you use [@Colx] because [Colx] would refer to the entire column.
That all said, what version of Excel are you using?