# Help! Excel question

What I need is to add a row of numbers except for one specific number.

For instance, if I have

450

450

450

99999

450

450

450

0

0

450

I want it to add the entire column without the 99999. The list I am working with is close to 8000 numbers. I know I can edit/replace but it would be nice if it just plucked out that one number somehow

=SUMIF(A:A, "<>99999")

It's really that easy huh? Thanks :)

Yup. Hardest part is remembering which bits need the quote marks around them and which don't.

DeThroned wrote:It's really that easy huh? Thanks :)

Yup. Hardest part is remembering which bits need the quote marks around them and which don't.

And remembering to add a comment to the field so anyone else who looks at the sheet will be alerted to the fact you have that so they don't get confused later.

You add comments to your cells? What a kind soul...

I don't.

Just out of curiosity, will that work if one of the numbers is 499999?

I.E. is what comes after the <> absolute or like a partial search?

I.E. is what comes after the <> absolute or like a partial search?

Absolute. What the <> means is basically: A value that isn't smaller than or bigger than 99999. Which leaves 99999.

Edit: Poorly phrased. <> is to Excel as != is to C++

fangblackbone wrote:I.E. is what comes after the <> absolute or like a partial search?

Absolute. What the <> means is basically: A value that isn't smaller than or bigger than 99999. Which leaves 99999.

Edit: Poorly phrased. <> is to Excel as != is to C++

I'm sure there's some kind of AND boolean logic function you could put in the if, so that it's if <> 999999 AND <> 499999, but I don't know the syntax for that.

Rezzy wrote:fangblackbone wrote:I.E. is what comes after the <> absolute or like a partial search?

Absolute. What the <> means is basically: A value that isn't smaller than or bigger than 99999. Which leaves 99999.

Edit: Poorly phrased. <> is to Excel as != is to C++I'm sure there's some kind of AND boolean logic function you could put in the if, so that it's if <> 999999 AND <> 499999, but I don't know the syntax for that.

Since everything after the comma is conditional criteria then (and I'd have to check this when I'm in work with access to excel) you should be able to stick any old conditional stuff in there - including IF and AND statements.

[edit] My mistake, you have to switch to SUMIFS then.

I'm sure there's some kind of AND boolean logic function you could put in the if, so that it's if <> 999999 AND <> 499999, but I don't know the syntax for that.

I don't think that's what he was asking, but here's the easily expanded version.

=SUMIFS(A:A, A:A, "<>99999", A:A, "<>499999")

With that version you can just keep adding additional criteria and they don't have to be in the same column.

/Curses! That's what I get for taking the time to doublecheck that it actually works (I usually don't reference the same column for my conditions).

=SUMIFS(A:A, A:A, "<>99999", A:A, "<>499999")

Whoa, that's bringing back firmly repressed memories of working with a bunch of Excel jockeys (a.k.a., financial types). I recall building a little web parser to untangle all that nested garbage, so when I went to their desks I could just paste the IF(blah,IF(blah,blah,blah),IF(jesus,blah,IF(blah,shootme,now))) into a box and see something sensible.

Whoa, that's bringing back firmly repressed memories of working with a bunch of Excel jockeys (a.k.a., financial types).

Then you definitely don't want to see the nested conditional statements I had to work out to pull demographic attendance data from monthly student rosters submitted from four shelter classrooms, and the new ethnicity values I have to figure out how to stack on top of that so that our State-Level overlords can tell if their money is directly affecting the education of Pacific Islander girls on IEPs in the 7th grade.

I'm THIIIIS close to shoving this BS into our SQL server and putting the report in a webapp behind our VPN.

Whoa, that's bringing back firmly repressed memories of working with a bunch of Excel jockeys (a.k.a., financial types). I recall building a little web parser to untangle all that nested garbage, so when I went to their desks I could just paste the IF(blah,IF(blah,blah,blah),IF(jesus,blah,IF(blah,shootme,now))) into a box and see something sensible.

I guess you don't want to see THIS!:

=IF(AND($F:$F>=$J$15,$F:$F<$J$16),IF(SEARCH("(",$B:$B)=8,MID($B:$B,SEARCH("(",$B:$B)-7,5)+0,IF(AND(SEARCH("(",$B:$B)=7,SEARCH("%",$B:$B)=6),MID($B:$B,SEARCH("(",$B:$B)-6,5)+0,IF(AND(SEARCH("(",$B:$B)=7,SEARCH("%",$B:$B)=5),MID($B:$B,SEARCH("(",$B:$B)-6,4)+0,IF(AND(SEARCH("(",$B:$B)=7,SEARCH("%",$B:$B)>7),MID($B:$B,SEARCH("(",$B:$B)-6,5)+0,IF(AND(SEARCH("(",$B:$B)=6,SEARCH("%",$B:$B)=5),MID($B:$B,SEARCH("(",$B:$B)-5,4)+0,IF(AND(SEARCH("(",$B:$B)=6,SEARCH("%",$B:$B)=4),MID($B:$B,SEARCH("(",$B:$B)-5,3)+0,IF(AND(SEARCH("(",$B:$B)=6,SEARCH("%",$B:$B)>6),MID($B:$B,SEARCH("(",$B:$B)-5,5)+0," ")))))))," ")

Which is a short version of the long version of a two step process to disentangle mistyped data entered into excel spreadsheets over the last 10 years, but which were never corrected, so we can perform data trending on the data as is the wont of the overlords all of a sudden after 20+ years of this company being in existence!

Before I came along they did it manually.... on a case-by case basis. Which is crazy! One project took a guy 20+ hours and, of course, if there are any more data added then he has to do it all again - especially if they can't be put into the same ranges.

Incredibly, I won an award for a much less complicated piece of work but because some international head of department saw it in a presentation it got a load of recognition whilst this is in the background for the time being. Next year I plan to get another prize if I manage this carefully enough