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 :

Num	Name	Debit	Credit
1	APPLE	1000		sum only Num 2 and 3
1	ABC		100	due to 1 contain "APPLE"
1	001		900
2	ABC	1000
2	410		100
2	555		900
3	Orange	1000
3	Mango		100
3	001		800
3	555		100

<colgroup><col><col span="3"><col></colgroup><tbody>
</tbody>

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.