Conditional Sum Formula

typewriterguy

New Member
Joined
Oct 29, 2008
Messages
24
Looking for help with another conditional sum formula.

When either AL6 or AM6 have a value that is not zero or null, I want the formula to sum AJ6, AL6, AM6.

When both AL6 and AM6 have a null/zero value, I want the formula to return a value of "-".

Desired results
AJ6: Original value = 500
AL6: Change1 value = <NULL>
AM6: Change2 value = 50
AP6: Formula <FORMULA>desired result = 550

AJ6: Original value = 500
AL6: Change1 value = <NULL>
AM6: Change2 value = <NULL>
AP6: Formula <FORMULA>desired result= "-"

I tried =IF((AL6,AN6),SUM(AJ6,AL6,AN6),"-"), but it returned a #VALUE message.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Try

=IF(OR(AL6=0,AL6="",AM6=0,AM6=""),SUM(AJ6,AL6,AM6),"-")
 

swinglow

Active Member
Joined
Mar 31, 2010
Messages
394
try:

=IF(AND(OR(ISBLANK(AL6),AL6=0),OR(ISBLANK(AM6),AM6=0),"-",SUM(AJ6,AL6,AN6)
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,344
If AL6 and AM6 are not null, you sum AJ6 AL6 AM6 (if theyre zero it wont affect the sum)

Try
=IF(AND(OR(AL6=0,AL6=""),OR(AM6=0,AM6="")),"-",IF(AND(AL6<>"",AM6<>""),SUM(AJ6,AL6,AM6)))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,403
Messages
5,511,154
Members
408,828
Latest member
Csmnvld

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top