I tried to use the Conditional sum wizard to return the total amount when
Dept=LAN and AFR and E no.=1. The formula below returns a value of 0.
I was expecting 300.


=SUM(IF($A$2:$A$4="AFR",IF($A$2:$A$4="LAN",IF($B$2 :$B$4=B2,$C$2:$C$4,0),0),0))

A B C
1 Dept E no. Amount
2 LAN 1 200
3 AFR 1 100
4 BRF 1 100

Try the below modified one.. (Array entered: Ctrl+Shift+Enter)

=SUM(IF($A$2:$A$4={"AFR","LAN"},IF($B$2:$B$4=B2,$C $2:$C$4,0),0),0)

If this post helps click Yes
---------------
Jacob Skaria


"nc" wrote:

I tried to use the Conditional sum wizard to return the total amount when
Dept=LAN and AFR and E no.=1. The formula below returns a value of 0.
I was expecting 300.


=SUM(IF($A$2:$A$4="AFR",IF($A$2:$A$4="LAN",IF($B$2 :$B$4=B2,$C$2:$C$4,0),0),0))

A B C
1 Dept E no. Amount
2 LAN 1 200
3 AFR 1 100
4 BRF 1 100

Try this normally entered formula:

Use cells to hold the criteria:

E2 = LAN
E3 = AFR

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A4,E2:E3,0))),--(B2:B4=B2),C2:C4)

--
Biff
Microsoft Excel MVP


"nc" wrote in message
...
I tried to use the Conditional sum wizard to return the total amount when
Dept=LAN and AFR and E no.=1. The formula below returns a value of 0.
I was expecting 300.


=SUM(IF($A$2:$A$4="AFR",IF($A$2:$A$4="LAN",IF($B$2 :$B$4=B2,$C$2:$C$4,0),0),0))

A B C
1 Dept E no. Amount
2 LAN 1 200
3 AFR 1 100
4 BRF 1 100