if, if(and), sumif or sumproduct

[pippip]

I'm trying to do a formula that does the following:

if A:A = "temp" and B:B = "a" then return 0, but if A:A = "temp" and B:B <> "a" then add C:C (but only those cells in C:C where A:A = "temp" and B:B <> "a").

So, in column A I have various categories (including "temp") and in column B those categories will either have an "a" against them or will be blank. In column C are the figures. In another cell I want to put the formula that will either return 0 (because every "temp" is matched by an "a") or will return the sum of those figures where "temp" is not matched by an "a").

I hope that all makes sense. I've searched around and tried different ways of doing this, but without luck. The stumbling block seems to be adding column C:C only where the two conditions are met.

Thanks in advance for your help.

 

[jdmc45]

=IF(AND(A1="temp",B1<>"a"),C1,0)

Fill down. Then sum C column

 

[pippip]

Thanks, but that doesn't do it. Perhaps I wasn't too clear. Here's a snapshot:

Col A | Col B | Col C
temp | a | 20
temp | a | 20
temp | | 20

In a single cell (let's say cell F25 though it could be any other) I want to return either 0 if all "temps" are matched with an "a" or, if not, the sum of those cells that are not matched with an "a". (Sorry about the layout, not sure how to post an image.)

In the above example the figure showing in F25 would therefore be 20 (but if all the "temp" entries were matched with an "a" the figure in F25 would be 0).

Hope that's clearer. Thanks again for any input - it's much appreciated.

 

[Jonmo1]

Try
=SUMPRODUCT(--(A1:A100="temp"),--(B1:B100 < > "a"),C1:C100)


Note,
You cannot use entire column refs like A:A, must use Row #s like A1:A100
Unless using XL2007 or higher
Even then it's not recommended.

Hope that helps.

 

[T. Valko]

I'm trying to do a formula that does the following:

if A:A = "temp" and B:B = "a" then return 0, but if A:A = "temp" and B:B <> "a" then add C:C (but only those cells in C:C where A:A = "temp" and B:B <> "a").

So, in column A I have various categories (including "temp") and in column B those categories will either have an "a" against them or will be blank. In column C are the figures. In another cell I want to put the formula that will either return 0 (because every "temp" is matched by an "a") or will return the sum of those figures where "temp" is not matched by an "a").

I hope that all makes sense. I've searched around and tried different ways of doing this, but without luck. The stumbling block seems to be adding column C:C only where the two conditions are met.

Thanks in advance for your help.

If you're using Excel 2007 or later...

=SUMIFS(C:C,A:A,"temp",B:B,"<>A")

 

[pippip]

Thanks Jonmo1 and T.Valko. I'm using excel 2007 so I tried both formulas.

Jonmo1's sumproduct formula works perfectly (after accepting excel's suggestion from its built-in error correction that <>"a" should be just <"a").

I tried T.Valko's sumifs formula but it didn't work; it just returned 0 even though there are "temp" cells not matched with "a" cells.

Thanks again for your input - I'm so glad to have a solution.

 

[T. Valko]

I tried T.Valko's sumifs formula but it didn't work; it just returned 0 even though there are "temp" cells not matched with "a" cells.

Thanks again for your input - I'm so glad to have a solution.

Hmmm....

The SUMIFS should have worked.

As you can see in the screencap the formula does return the correct result.

You may have some kind of data problem.

 

[T. Valko]

Jonmo1's sumproduct formula works perfectly (after accepting excel's suggestion from its built-in error correction that <>"a" should be just <"a").

Actually, that's not correct.

The correct syntax is: <>"A". Which means: not equal to "A".

If Excel tried changing it to: <"A" that means: less than "A" (which is a valid test comparison).

If it works its by "dumb luck".

An empty cell will evaluate to be less than "A".

 

[pippip]

Hmmm....

The SUMIFS should have worked.

As you can see in the screencap the formula does return the correct result.

You may have some kind of data problem.

Just tried it again and it worked. Perhaps I entered something incorrectly in my earlier attempts.

Thanks again.

 

[pippip]

Actually, that's not correct.

The correct syntax is: <>"A". Which means: not equal to "A".

If Excel tried changing it to: <"A" that means: less than "A" (which is a valid test comparison).

If it works its by "dumb luck".

An empty cell will evaluate to be less than "A".

I thought it was strange it didn't accept the 'not equal to' syntax and preferred the 'less than' syntax. I'm happy with dumb luck when it works.

And now I have two solutions and my understanding of excel continues to grow.