Sum with multiple conditions

[Nath]

i am trying to use this formula, but works with only the first 2 conditions.
=SUM(IF(('BB Data Files 0506.xls'!Ren_Date>D10-30)*('BB Data Files 0506.xls'!Ren_Date<D10+1)*('BB Data Files 0506.xls'!Pack="Waived"),'BB Data Files 0506.xls'!ValueR))
Any help will be greatly apprciated

 

[Aladin Akyurek]

i am trying to use this formula, but works with only the first 2 conditions.
=SUM(IF(('BB Data Files 0506.xls'!Ren_Date > D10-30)*('BB Data Files 0506.xls'!Ren_Date < D10+1)*('BB Data Files 0506.xls'!Pack="Waived"),'BB Data Files 0506.xls'!ValueR))
Any help will be greatly apprciated

=SUM(IF('BB Data Files 0506.xls'!Ren_Date > D10-30,IF('BB Data Files 0506.xls'!Ren_Date < D10+1,IF('BB Data Files 0506.xls'!Pack="Waived",'BB Data Files 0506.xls'!ValueR))))

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

If this does not yield the desired outcome, try to specify the named ranges in detail.

 

[Nath]

i am trying to use this formula, but works with only the first 2 conditions.
=SUM(IF(('BB Data Files 0506.xls'!Ren_Date > D10-30)*('BB Data Files 0506.xls'!Ren_Date < D10+1)*('BB Data Files 0506.xls'!Pack="Waived"),'BB Data Files 0506.xls'!ValueR))
Any help will be greatly apprciated

=SUM(IF('BB Data Files 0506.xls'!Ren_Date > D10-30,IF('BB Data Files 0506.xls'!Ren_Date < D10+1,IF('BB Data Files 0506.xls'!Pack="Waived",'BB Data Files 0506.xls'!ValueR))))

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

If this does not yield the desired outcome, try to specify the named ranges in detail.

Thanks for the suggestion. Unfortunately it did not work.
The rages are
Multi column range with 26 columns running to 2500 rows. The formula concerns 2 columns
1. Renewal Date - Date of renewal
2. Pack - check if the charges are waived
3. Renewal Amount - Sum values in this column
i am trying to sum data to sum data that falls within 2 dates and where the renewal charges are waived.
i hope this will enable you to come up with a solution. Thanks in advance

 

[ken2step]

Book1
	A	B	C	D
1	Beg Date	End Date	Solution
2	1-Dec	24-Dec	$99.00
3
4	Renewal Date	Charges Waived	Amount
5	November-05	x	$15.00
6	December-05		$15.00
7	November-05		$15.00
8	December-05	x	$15.00
9	October-05		$10.00
10	September-05	x	$10.00
11	May-05		$20.00
12	December-05		$30.00
13	March-05	x	$35.00
14	September-05		$42.00
15	May-05	x	$49.00
16	December-05		$56.00
17	March-05	x	$63.00
18	September-05	x	$70.00
19	May-05		$77.00
20	December-05	x	$84.00
Sheet1

 

[Aladin Akyurek]

i am trying to use this formula, but works with only the first 2 conditions.
=SUM(IF(('BB Data Files 0506.xls'!Ren_Date > D10-30)*('BB Data Files 0506.xls'!Ren_Date < D10+1)*('BB Data Files 0506.xls'!Pack="Waived"),'BB Data Files 0506.xls'!ValueR))
Any help will be greatly apprciated

=SUM(IF('BB Data Files 0506.xls'!Ren_Date > D10-30,IF('BB Data Files 0506.xls'!Ren_Date < D10+1,IF('BB Data Files 0506.xls'!Pack="Waived",'BB Data Files 0506.xls'!ValueR))))

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

If this does not yield the desired outcome, try to specify the named ranges in detail.

Thanks for the suggestion. Unfortunately it did not work.
The rages are
Multi column range with 26 columns running to 2500 rows. The formula concerns 2 columns
1. Renewal Date - Date of renewal
2. Pack - check if the charges are waived
3. Renewal Amount - Sum values in this column
i am trying to sum data to sum data that falls within 2 dates and where the renewal charges are waived.
i hope this will enable you to come up with a solution. Thanks in advance

Can you post an example date that you have in D10?

May I also suggest that you run ASAP Utilities (asap-utilities.com) on your data in order to clean it up from extraneous spaces, other invisble chars and to convert possibly text-formatted numeric data to true numeric data?

 

[Nath]

D5 has this value: 30-06-05

 

[Aladin Akyurek]

D5 has this value: 30-06-05

You test the Ren_Date for being >= D5-30 and < D5+1 in your initial formula. It looks like you want a conditional sum regarding Jun-05. If so, try:

=SUMPRODUCT(--('BB Data Files 0506.xls'!Ren_Date-DAY('BB Data Files 0506.xls'!Ren_Date)=D5),--('BB Data Files 0506.xls'!Pack="Waived"),'BB Data Files 0506.xls'!ValueR)

where D5 houses the first day date of the month-year of interest, tha is,

1-Jun-05

BTW, try to take note of running ASAP Utilities on your data as I suggested in my previous post.

 

[Nath]

The original formula works with the 2 date conditions , the problem comes when the third condition is added . The same thing happens whith 2 conditions with Pack status as one of the conditions. I thought empty cells might be creating a problem but the problem persists when all cell are filled up . I did download ASAP utilities , but i have not any clean up option .
Thanks once again for taking the trouble