Ask Your Question
0

How to use sumif with condition 'not in list'?

asked 2015-01-27 21:55:05 +0100

Herbivore13 gravatar image

updated 2015-01-27 21:56:03 +0100

Hello,

I try to use sumif (actually, sumifs, but this should have no impact on my problem) but the condition I want to use is 'if X is not in list Y' and Y is a range of cells. Example:

          A                      B                    C

1       Apple              Apple              2

2       Banana           Apple              2

3       Pineapple       Banana           3

4                               Apple               2

5                               Broccoli           5

6                               Pineapple        13

(I'm sorry for the horrible implementation of that table here ;).)

Now, I wanna add all numbers from column C if the corresponding cell in column B is not in the list from A1:A3.

In this case, it's only Broccoli in B5.

Of course, I could use one of the following manual solutions:

=SUMIFS(D1:D6, C1:C6, "<>" & A1, C1:C6, "<>" & A2, C1:C6, "<>" & A3)

=SUMIFS(D1:D6, C1:C6, "<>Apple", C1:C6, "<>Banana", C1:C6, "<>Pineapple")

(Although I don't have a clue why you have to type "<>" & A1 instead of just <>A1. If you can tell me that, I'd be thankful!)

But I'm a bit lazy and also interested in if and how it's possible to automize it a bit.

Actually, I want to use sumifs and I'm also using a condition to check the date. I'm creating a 'fancy' spreadsheet to calculate on what topics I'm spending money and this is for some nice statistics.

I am using Kubuntu 10.14 and LibreOffice Calc Version: 4.3.3.2.

Best regards

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-01-27 22:28:44 +0100

Lupp gravatar image

updated 2015-01-28 19:29:52 +0100

Thanks to @Wildcard : I edited the example formula to avoid the misleading effect (explaind in Addendum2) of the original one.

As the "criteria" concept is not very flexible you might better use

=SUMPRODUCT(D1:D11;ISERROR(MATCH(C1:C11;B1:B11;0)))

=SUMPRODUCT(D1:D11;ISERROR(MATCH(C1:C11;RangeToLookupIn_B1:B5_eg;0)))

(I kept the item numbers in column A and did not delete the empty rows.) This way you may use extremely refined conditions. If you dislike relying on the implicit retyping of a the logical values into numerical ones, you may replace the part concerned with an IF() function call returning 1 or 0 respectively.

Addendum

Please note: SUMPRODUCT (meaning a sum of products) is one of the functions having parameters specified as 'ForceArray'. Placing the range C1:C11 corresponding in size and dimension with D1:D11 on a parameter position regularly accepting only a scalar, will therefore cause MATCH to return a complete array of results (1 for any of the eleven rows) instead of just one scalar value.

Addendum2

For MATCH() only the first parameter is specified to accept a scalar. Placing an array there, will mostly cause an error under the condition of "normal" evaluation expecting a scalar result returned by MATCH(). Under the condition of an "array-evaluation", however, the function will be applied to all the elements of the array one by one and will return an equally sized and equally dimensioned array of values. In the case under discussion this array will be passed to the next stage of the evaluation which is applying ISERROR(). For the same reason (still array-mode) this logical function will now return an array instead of a (logical) scalar. The second parameter of MATCH() being an array of arbitrary size (1D) anyway will not be affected by the specifics of the array mode here.

edit flag offensive delete link more

Comments

Thank you for your reply!

I still have to figure out how MATCH works, but I'm afraid that this particular solution wouldn't work in my case.

Column B and C are (in my case) two lists representing the receipts I got in the shops. They will always have the same dimension. But column A has something else in the row below 'Pineapple' (which doesn't belong to that list). I could just use a column Q (one that is not used) and have that list there, but I'm not a big friend of this solution.

Herbivore13 gravatar imageHerbivore13 ( 2015-01-28 00:03:14 +0100 )edit

And ... of course, this isn't about fruits. I'm crazy, but not that crazy ;). Fruits are just an example.

Edit: I forgot to mention that the list B and C (or C and D in your case) have an arbitrary length. And column A would be - actually - limited and only expanded when I want to add another fruit to that list.

Herbivore13 gravatar imageHerbivore13 ( 2015-01-28 00:06:20 +0100 )edit

Actually, it WILL work for you! :) I just checked; the "criteria" list doesn't have to match the others in dimensions for the formula to work. And if B and C are arbitrary length, then just put in a big number like 100. So you can use:

=SUMPRODUCT(C1:C100,ISERROR(MATCH(B1:B100,A1:A3,0)))

Of course, you'll have to update this if you extend the criteria list (A). So maybe change that A3 to A10 or something, to leave you some extra space.

Wildcard gravatar imageWildcard ( 2015-01-28 09:53:23 +0100 )edit

@Wildcad is completely right. I want to apologise for misleading any body using an equally sized range for the entries to search among. The arry evaluation will only apply to the range of the values to be searched for . The first parameter of MATCH() used for such a value is originally specified to accept a scalar.

Lupp gravatar imageLupp ( 2015-01-28 14:32:57 +0100 )edit

Ah, great! For a stupid reason, I was trying this by myself, but changed the C (or D) parameter but not the A (or B) parameter! D'oh!

Thank you both a lot! And Lupp, no need to apologize :). I'll read your explanation (addendum 2) later again and if there are no further questions, I think this issue has been solved. Thanks again! :)

Herbivore13 gravatar imageHerbivore13 ( 2015-01-30 11:24:10 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-01-27 21:55:05 +0100

Seen: 10,143 times

Last updated: Jan 28 '15