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

 

[jtakw]

Hi,

Are you talking about the "Debit" or "Credit" Column?

 

[Aladin Akyurek]

In E2 control+shift+enter, not just enter and copy across to F2:

=SUM(IF(ISNA(MATCH($A$2:$A$11,IF($B$2:$B$11="apple",$A$2:$A$11),0)),C$2:C$11))

 

[Vanda_a]

Hi,

Are you talking about the "Debit" or "Credit" Column?

Good morning. Yes sum both columns

Thank you

 

[Vanda_a]

In E2 control+shift+enter, not just enter and copy across to F2:

=SUM(IF(ISNA(MATCH($A$2:$A$11,IF($B$2:$B$11="apple",$A$2:$A$11),0)),C$2:C$11))

Good morning. It works. Thank you very much. May i know what is ISNA is used in this formula?

 

[Vanda_a]

In E2 control+shift+enter, not just enter and copy across to F2:

=SUM(IF(ISNA(MATCH($A$2:$A$11,IF($B$2:$B$11="apple",$A$2:$A$11),0)),C$2:C$11))

Could you help explain why i can not invoke your formula into mine?

=SUM(IF(('All JL'!$B:$B="SINV")*(LEFT('All JL'!$E:$E,4)="PNN-")*(LEFT('All JL'!$F:$F,2)="41")+('All JL'!$G:$G="Customers' Deposits")*(ISNA(MATCH('All JL'!$E:$E,IF('All JL'!$G:$G="VAT-Output",'All JL'!$E:$E),0))),'All JL'!$I:$J))

I am trying to sum all transactions that contain no VAT-Output but the transactions has to meet some criteria as well. B:B="SINV", E:E first 4 letter ="PNN-", F:F first 2 letter = "41" or G:G = Customers' Deposits

Thank you

 

[Aladin Akyurek]

1. You haven't told the outcome which obtains.

2. Avoid whole column referencing for efficiency reasons.

3. Why + ?

4. Does the following succeed? Control+shift+enter…

=SUM(IF(ISNA(MATCH('All JL'!$E$2:$E$100,IF('All JL'!$G$2:$G$100="VAT-Output",'All JL'!$E$2:$E$100),0)),IF((LEFT('All JL'!$E$2:$E$100,4)="PNN-")*('All JL'!$B$2:$B$100="SINV")*(LEFT('All JL'!$F$2:$F$100,2)="41")*('All JL'!$G$2:$G$100="Customers' Deposits"),'All JL'!$I$2:$J$100)))

 

[Vanda_a]

1. You haven't told the outcome which obtains.

2. Avoid whole column referencing for efficiency reasons.

3. Why + ?

4. Does the following succeed? Control+shift+enter…

=SUM(IF(ISNA(MATCH('All JL'!$E$2:$E$100,IF('All JL'!$G$2:$G$100="VAT-Output",'All JL'!$E$2:$E$100),0)),IF((LEFT('All JL'!$E$2:$E$100,4)="PNN-")*('All JL'!$B$2:$B$100="SINV")*(LEFT('All JL'!$F$2:$F$100,2)="41")*('All JL'!$G$2:$G$100="Customers' Deposits"),'All JL'!$I$2:$J$100)))

Good afternoon. it works now. Thank you very much. + sign because i would like to have or because if F:F is false then look up G:G.
By the way, may i know what is ISNA does in the formula?

 

[Aladin Akyurek]

Good afternoon. it works now. Thank you very much. + sign because i would like to have or because if F:F is false then look up G:G.

Is this + still needed?

By the way, may i know what is ISNA does in the formula?

IF(ISNA(MATCH('All JL'!$E$2:$E$100,IF('All JL'!$G$2:$G$100="VAT-Output",'All JL'!$E$2:$E$100),0))

reads:

Filter for, i.e. select, the E cells which do not correspond to any G cell that equals VAT-Output. Thus:

If an E cell from the E range is not in the sublist of E cells for which G = VAT-Output (constructed by the inner IF in the quoted expression), execute the rest of the larger formula.

 

[Vanda_a]

Is this + still needed?

Thanks for your explanation and Yes + sign still needed. It outcome the right figure.