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

 

[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.