SUM a row IF conditions apply

[Chryso]

Could someone please help?
Need to sum up all values in a column based certain conditions. See example below:

Amount Status_1 Status_2 REF
500 YES PENDING POP
1200 YES PAID POP
2500 YES PENDING BOC
8000 NO PENDING BOC
1800 YES PENDING POP

The formula to sum up all amounts that fulfill the following three condtions
IF,
Status_1 = YES
Status_2 = PENDING
REF = POP

In this example, the result should be €2.300 (ie adding row 1 and row 5)

Please helppppp!

 

[VoG]

Try

=SUMPRODUCT(--(B2:B6="YES"),--(C2:C6="PENDING"),--(D2:D6="POP"),A2:A6)

 

[Aladin Akyurek]

Could someone please help?
Need to sum up all values in a column based certain conditions. See example below:

Amount Status_1 Status_2 REF
500 YES PENDING POP
1200 YES PAID POP
2500 YES PENDING BOC
8000 NO PENDING BOC
1800 YES PENDING POP

The formula to sum up all amounts that fulfill the following three condtions
IF,
Status_1 = YES
Status_2 = PENDING
REF = POP

In this example, the result should be €2.300 (ie adding row 1 and row 5)

Please helppppp!

Try one of...

=SUMPRODUCT(RangeToSum,--(RangeStatus_1="Yes"),--(RangeStatus_2="Pending"),--(RangeREF="Pop"))

=SUMIFS(RangeToSum,RangeStatus_1,"Yes",RangeStatus_2,"Pending",RangeREF,"Pop")

 

[Chryso]

The "SUMPRODUCT" function works but doesn't give the correct result,
the "SUMIFS" returns a #VALUE command

 

[VoG]

SUMPRODUCT working here

Excel Workbook
	A	B	C	D	E
1	Amount	Status_1	Status_2	REF	2300
2	500	YES	PENDING	POP
3	1200	YES	PAID	POP
4	2500	YES	PENDING	BOC
5	8000	NO	PENDING	BOC
6	1800	YES	PENDING	POP
Sheet10

SUMIFS is only available in Excel 2007 and later.

 

[Chryso]

A BIG BIG Thank you guys!

 

[fterry]

This also helped me as "Sum(If(And" wasn't working. My question is what are the dashes doing in the formula "(--(B2:B6="YES")"? It doesn't work without them so I was just wondering what their function is.

Thanks.

 

[Aladin Akyurek]

This also helped me as "Sum(If(And" wasn't working. My question is what are the dashes doing in the formula "(--(B2:B6="YES")"? It doesn't work without them so I was just wondering what their function is.

Thanks.

=SUMPRODUCT(RangeToSum,--(RangeStatus_1="Yes"),--(RangeStatus_2="Pending"),--(RangeREF="Pop"))

Or...

=SUMPRODUCT(A2:A6,--(B2:B6="YES"),--(C2:C6="PENDING"),--(D2:D6="POP"))

is equivalent to:

=SUM(IF(RangeStatus_1="Yes",IF(RangeStatus_2="Pending",IF(RangeREF="Pop",RangeToSum))))

which must be confirmed with control+shift+enter, not just enter.

Here are some links on the -- bit:

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=128907

http://www.mrexcel.com/forum/showthread.php?t=70547