Sum with multiple conditions

Nath

New Member
Joined
Dec 18, 2005
Messages
7
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
 

Some videos you may like

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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Dec 18, 2005
Messages
7
Aladin Akyurek 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
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Nath said:
Aladin Akyurek 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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Dec 18, 2005
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,948
Members
412,299
Latest member
agentless
Top