A SUMIF() I think

[tilltek]

I have been given the task of adding to an existing spreadsheet.
The problem I have is I think quite simple but I can't get my head around it.

1 | A | B | C
2 | 100 | 201 | =SUM(A2:A5)
3 | 235 | 203 |
4 | 1,232| 309 |
5 | 1,125| 201a |

At the moment the sheet has the above simple sum in C2 to add column A
Column B holds flags that allow the figures to be collected elsewhere.

My problem is, how do I sum C2 ONLY if there is no "a" in the column B flag.
In other words, in the above sample, add only 100, 235, 1,232 but not 1,125 because its flag has an "a".

Clear as mud?

Kenny F

 

[Gavona]

201a is text not a number. If the other entries in B are numeric then this might work for you:
=SUMPRODUCT(A1:A6,-ISNUMBER(B1:B6))



Gavin

 

[Gavona]

Clearly you will need to adjust the range in the example I just posted. Alternatively, using wild cards within SUMIF:
=SUMIF(B1:B6,"<>*a",A1:A6)

For more about wildcards in Excel formulae: thread68-1253790 the examples in one of KenWright's posts may be particularly helpful to you.


Gavin

 

[SkipVought]

1) is "a" the ONLY non-numeric used in that column and 2) is it ALWAYS the ONLY alpha character and 3) is it ALWAYS the LAST character?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

[tab][COLOR=blue white]=SUMIF(B:B, "<>*a*",A:A )[/color]

That will Sum all values in Column A, but only where Column B does NOT contain the letter "A" in any position. It is case insensitive.


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Sorry, Gavin - I somehow missed your second post.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

Hey John, that's no issue. I forgot about wildcards when I first responded but suspect the problem will require a more advanced solution - await the OP's response to Skip's questions....And some awe inspiring regular expression or array formula solution!

Gavin

 

[tilltek]

Thanks for all your help but before I could get stuck into it the guy decided he didn't need it after all.
Kenny