sum+if

[Vanda_a]

Dear all

Could you help me with this issue please?
I would like to sum only the transaction that does not contain "APPLE"
Ex :
[TABLE="class: grid, width: 489"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Num[/TD]
[TD]Name[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]APPLE[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]sum only Num 2 and 3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]due to 1 contain "APPLE"[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001[/TD]
[TD][/TD]
[TD]900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]410[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]555[/TD]
[TD][/TD]
[TD]900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Orange[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mango[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001[/TD]
[TD][/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]555[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much

 

[Vanda_a]

Is this + still needed?

Good morning. I have added an other criteria into inner if but why it works this way and not that way?
IF(('All JL'!$B:$B="BILL")*('All JL'!$F:$F<>"2100030007"),'All JL'!$I:$J))) ---- work
IF(('All JL'!$B:$B="BILL")*(LEFT('All JL'!$F:$F,2)<>"21"),'All JL'!$I:$J))) ----- not work

Thank you

 

[joeu2004]

IF(('All JL'!$B:$B="BILL")*('All JL'!$F:$F<>"2100030007"),'All JL'!$I:$J))) ---- work
IF(('All JL'!$B:$B="BILL")*(LEFT('All JL'!$F:$F,2)<>"21"),'All JL'!$I:$J))) ----- not work

Forgive the interjection. Just an observation when I stumbled across this discussion, which I am not following.

It is very bad to use whole-column ranges like B:B in formulas like that.

In Excel 2007 and later, it causes Excel to build arrays with 1+ million entries. In this case, Excel will create at least 3 arrays 1+ million entries each concurrently. And each "entry" is multiple bytes, since you have text values. So you will need more than 24 MB to process each of those formulas.

Besides the excess computer memory, processing that much data can slow performance noticeably. It is probably the explanation when users complain that Excel is "not responding".

It is much better to use limited ranges. Even if you do not know how many rows of data you might, you probably know you will not have 1+ million rows; perhaps not even more than 10,000 rows; and perhaps much less. Even B1:B10000 is much better than B:B.

This probably has nothing to do with the problem that you are having.

 

[Vanda_a]

Forgive the interjection. Just an observation when I stumbled across this discussion, which I am not following.

Aladin advised me once. Anyway thanks for the advise. Let me try it if it works

 

[Aladin Akyurek]

Good morning. I have added an other criteria into inner if but why it works this way and not that way?
IF(('All JL'!$B:$B="BILL")*('All JL'!$F:$F<>"2100030007"),'All JL'!$I:$J))) ---- work
IF(('All JL'!$B:$B="BILL")*(LEFT('All JL'!$F:$F,2)<>"21"),'All JL'!$I:$J))) ----- not work

Thank you

These IF bits do not test the same thing. The test 'All JL'!$F:$F<>"2100030007" will pick out all of the numeric values in column F including the numeric value 2100030007, while (LEFT('All JL'!$F:$F,2)<>"21") will negate (exclude) anything that starts with 21, numeric or not.

 

[joeu2004]

Aladin advised me once

Yes, in post #7 , which I overlooked. My bad!

 

[Vanda_a]

Yes, in post #7 , which I overlooked. My bad!

Anyway thank you

 

[Vanda_a]

These IF bits do not test the same thing. The test 'All JL'!$F:$F<>"2100030007" will pick out all of the numeric values in column F including the numeric value 2100030007, while (LEFT('All JL'!$F:$F,2)<>"21") will negate (exclude) anything that starts with 21, numeric or not.

Good afternoon. I have try not to use full column and it works now but may i check this issue please? when it work and when it doesn't? <>{"2100030007","2100040005"}. I tried normal sumif with this trick and it works, why it does not work in my case?
IF(ISNA(MATCH('All JL'!$E$2:$E$100,IF('All JL'!$G$2:$G$100={"VAT-Output","WHT Payable"},'All JL'!$E$2:$E$100),0))
Thank you very much

 

[Aladin Akyurek]

Good afternoon. I have try not to use full column and it works now but may i check this issue please? when it work and when it doesn't? <>{"2100030007","2100040005"}. I tried normal sumif with this trick and it works, why it does not work in my case?
IF(ISNA(MATCH('All JL'!$E$2:$E$100,IF('All JL'!$G$2:$G$100={"VAT-Output","WHT Payable"},'All JL'!$E$2:$E$100),0))
Thank you very much

We need:

IF(ISNA(MATCH('All JL'!$E$2:$E$100,IF(ISNUMBER(MATCH('All JL'!$G$2:$G$100,{"VAT-Output","WHT Payable"},0)),'All JL'!$E$2:$E$100),0))

 

[Vanda_a]

We need:

IF(ISNA(MATCH('All JL'!$E$2:$E$100,IF(ISNUMBER(MATCH('All JL'!$G$2:$G$100,{"VAT-Output","WHT Payable"},0)),'All JL'!$E$2:$E$100),0))

Hello. May I know when should I use the brackets and when not?

Thank you

 

[Aladin Akyurek]

Hello. May I know when should I use the brackets and when not?

Thank you

[1]
{"VAT-Output","WHT Payable"}

is equivalent of

[2]
X2:Y2

where X2 = VAT-Output and Y2 = WHT Payable.

We could have used a range like X2:Y2, but when the range is small as here, [1], the hard-coded array form, is also chosen, as we have done.