# Sum with multiple conditions

#### Nath

##### New Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### MrExcel MVP
Nath said:
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

##### New Member
Nath said:
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

##### Well-known Member
Book1
ABCD
1Beg DateEnd DateSolution
21-Dec24-Dec\$99.00
3
4Renewal DateCharges WaivedAmount
5November-05x\$15.00
6December-05\$15.00
7November-05\$15.00
8December-05x\$15.00
9October-05\$10.00
10September-05x\$10.00
11May-05\$20.00
12December-05\$30.00
13March-05x\$35.00
14September-05\$42.00
15May-05x\$49.00
16December-05\$56.00
17March-05x\$63.00
18September-05x\$70.00
19May-05\$77.00
20December-05x\$84.00
Sheet1

##### MrExcel MVP

Nath said:
Nath said:
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

##### New Member
D5 has this value: 30-06-05

##### MrExcel MVP
Nath said:
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

##### New Member
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

Replies
1
Views
78
Replies
0
Views
43
Replies
1
Views
26
Replies
8
Views
61
Replies
2
Views
86