Forum Home
Press F1
Thread ID: 36313	2003-08-06 00:51:00	EXCEL Conditional Sum	GrahamB (750)	Press F1

Post ID	Timestamp	Content	User
165653	2003-08-06 00:51:00	I have a series of dates in range B3:B10, with values in range C3:C10 . In Range B20: B300 I have a sequence of dates. I need a formula which looks at the date in the appropriate row of Range B20:B300 (say B100) and then sums the range C3:C10 if Value of B100 is greater than (not >=) the range B3:B10). I do not seem to be able to get the normal sumif to generate the formula in a way that enables me to drag the formula through the range B20:B300 Any assistance will be appreciated. TFYH Graham Bockett	GrahamB (750)
165654	2003-08-06 02:13:00	Hi Graham, I need a formula which looks at the date in the appropriate row of Range B20:B300 (say B100) and then sums the range C3:C10 if Value of B100 is greater than (not >=) the range B3:B10). So you want to sum c3:c10 IF the date value in B100 > date values in b3:b10. Which date value in b3:b10? One date may be before, one may be after. You will need to define this part of what you want to do. Post info about this and I will try and help you out.	parry (27)
165655	2003-08-06 03:00:00	> I have a series of dates in range B3:B10, with values > in range C3:C10 . > > In Range B20: B300 I have a sequence of dates. > > I need a formula which looks at the date in the > appropriate row of Range B20:B300 (say B100) and then > sums the range C3:C10 if Value of B100 is greater > than (not > =) the range B3:B10). > > I do not seem to be able to get the normal sumif to > generate the formula in a way that enables me to drag > the formula through the range B20:B300 =IF(B100 > SUM($B$3:$B$10),SUM($C$3:$C$10),) However this would have to be in a different column such as the A column	roofus (483)
165656	2003-08-06 04:07:00	Hi, further to the formula put down by Roofus, it works well for numbers but gives odd results for dates. If you sum dates they will always be bigger than an individual date unless the difference is thousands of years. Excel uses serial numbers to determine dates. As an example Today is serial 37,839 and if I summed two dates, say yesterday and the day before yesterday, I get 75,675. So that means today is less than yesterday and the day before!! er, no it isnt :-) You could do this as an array, which treats each date separately, but I think there is more to it as why would you sum dates at all? Perhaps what is required is to see whether the date is bigger/smaller than all of those dates in the range as a group. Would need to understand whats is really required. Sorry Roofus, dont want to offend you but thought I should point this out ;-)	parry (27)
165657	2003-08-06 07:13:00	i hear what your saying parry, but i was under the impression that it wasn't dates in the cells that were been referenced, otherwise what you say is true	roofus (483)
165658	2003-08-06 07:38:00	With B3:B10 containing dates, the formula should be =IF(B100 > MAX($B$3:$B$10),SUM($C$3:$C$10),) > =IF(B100 > SUM($B$3:$B$10),SUM($C$3:$C$10),)	Russell D (18)
165659	2003-08-06 12:21:00	I obviously haven't made myself clear! The Dates in Range b3:b10 are non-consecutive, and represent the date at which the value in C3:C10 is added to an investment pool. The dates in Range b20:b300 are consecutive dates. The Sum value is the value to which the daily interest is to be applied. (The '>' indicates that interest starts to apply the day after the money goes in the fund.) So the B3 may be 1st Aug, C3 $100 B4 may be 28 Aug, C4 $400 The Sum for B20 (1 Aug) will be $0, B21 et seq will be $100, and B49 will be $500. Hope that makes it clearer what I am trying to achieve. For what its worth the interest is 1.9% per day!!!!! TFYH Graham	GrahamB (750)
165660	2003-08-06 23:06:00	How about an array formula: {=SUM(IF(B$3:B$10<B20,C$3:C$10))} This is the formula for C20 - type it without the braces then use CTRL+SHIFT+ENTER to enter it as an array formula.	wuppo (41)
165661	2003-08-06 23:45:00	or for the purists... {=SUM(IF(B$3:B$10>=B20,0,C$3:C$10))} :|	wuppo (41)
165662	2003-08-07 00:11:00	Hi again, just as an aside are you listing dates in cells B20:b300 just to work out a time lapse? Did you know Excel has built in functions for working out interest payments (see Financial category when you click the fx function button on the toolbar). Just saves you listing lots of dates solely for this. You could work out interest from say 1/8 to today like this, where B3 is 1/8/03 and c3 is $100 =((DATEDIF(B3,NOW(),"D"))*C3)*0.0196/365 The DATEDIF function calculates how many days difference between two dates.	parry (27)
1 2