1

I need to make a formula in Excel which sums the values of the previous 6 cells. The complicated part is that if there are only null values, the total should be a hyphen: "-". I got this to work, but then realized there are two possible null values: hyphen and blank. Here is the formula I created for the hyphen as the null value. It worked how I wanted. But then several cells came out with a "0" since they were summing both types of null values. 

=IF(OR(K3 ="-",L3 ="-", M3 ="-", N3 ="-",O3 ="-", P3 ="-"), "-", SUM(K3:P3))

When I realized there were two null values, I tried adding an AND and another OR, but neither got the intended result. 

=IF(AND(OR(K3 ="-",L3 ="-", M3 ="-", N3 ="-",O3 ="-", P3 ="-"), K3 ="",L3 ="", M3 ="0", N3 ="",O3 ="", P3 =""), "-", SUM(K3:P3))

Any ideas on how to get that "blank" value in there? Thanks!

Improve this question
1
  • nice job by the way in asking the question with showing what you tried. Commented May 12, 2016 at 19:22

2 Answers 2

Reset to default
1

Try this:

=IF(SUMPRODUCT((K3:P3="-")+(K3:P3=""))=COLUMNS(K3:P3)*ROWS(K3:P3),"-",SUM(K3:P3))

This is effectively counting any cells are numbers. If it is 0, or in other words, if no numbers exist then it returns - else it returns the sum of those values.

Or this more concise formula:

=IF(SUMPRODUCT(1*(ISNUMBER(K3:P3)))>0,SUM(K3:P3),"-")
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I think I didn't explain well enough. I want to calculate the sum if there are any numbers at all. I want "-" if all of the values are either "-" or "".
Somehow this isn't working for me. I have a line where four cells are "-", one cell is blank, and one cell has a number. This formula returns "-" when it should return the number in that one cell.
what cell has the issue? what is the combination? Edit your question, and add update to the bottom, and you can use a tool like this to copy and paste your date from excel. It has some issues with empty cells but if you temporarily fill them with * and then delete them in your post, its perfect.
@abmiller8 see edit I went back closer to the original.
@ScottCraner sorry, I can only do so much to indicate yours answer is far superior to the one I posted.
|
1

Going for the overly verbose formula now:

=IF(AND(OR(K3="-",K3=""),OR(L3="-",L3=""),OR(M3="-",M3=""),OR(N3="-",N3=""),OR(O3="-",O3=""),OR(P3="-",P3="")),"-",SUM(K3:P3))

That would be the correction to your original thought process.

Now if you want to be on the really safe side and look out for leading and trailing spaces, or cells filled with just spaces then try this:

=IF(AND(OR(TRIM(K3)="-",TRIM(K3)=""),OR(TRIM(L3)="-",TRIM(L3)=""),OR(TRIM(M3)="-",TRIM(M3)=""),OR(TRIM(N3)="-",TRIM(N3)=""),OR(TRIM(O3)="-",TRIM(O3)=""),OR(TRIM(P3)="-",TRIM(P3)="")),"-",SUM(K3:P3))

If I am missing a bracket in there apologies.

See Scotts answer for a better more condensed formula. His is easier to expand (or contract) should the number of columns change.

Improve this answer

6 Comments

The SUM() automatically skips nulls and strings to only sum the numbers. there is no reason to use the SUMPRODUCT(). Also this will throw an error when any of the cells have any text. You can't multiply a text value.
yeah I just tested it and saw that error. Was going to delete anyhow as it wound up being too similar to your edit.
That is to keep us from self flagellation or aggrandizing. there have been plenty of the first for me.
This formula works now. Thanks so much for your help!
The current trim formula while it works in a sense is flawed, all the closing brackets are in the wrong spot for the trim. I am about to correct that though.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.