need a formula to calculate column depending on date

[fairkatrina]

Hi, I'm struggling a bit with a formula - not even sure if it's possible?? what I want to do is have a list of works ongoing for my company ordered by completion date (column A) and value (column B) on sheet 1. On sheet 2 I want to be able to predict income over the upcoming weeks/months by showing the planned values for each week. there I need a formula which goes something like:

on sheet 1, if column A is between (date 1) and (date 2), count total of column B

can anyone help??

 

[cornflakegirl]

What version are you using?

If you have SUMIFS you can use that, otherwise you can use SUMPRODUCT, eg:

=SUMPRODUCT(--(A1:A10>=Date1),--(A1:A10<=Date2),B1:B10)

 

[fairkatrina]

hi, that is returning a value of 0. (using sumif as I don't have sumproduct). is the >= formula not related to numbers, not dates? is 'on or before' (i.e. date formula) written the same as 'greater than or equal to' (i.e. number formula) ?

 

[cornflakegirl]

SUMIF won't work, as it can only handle one condition.
SUMIF<b>S</b> would work, but you need 2007 or 2010 for that one.
If you're using an earlier version, you'll have to use SUMPRODUCT. I'm not sure why you think you don't have it?

(Dates are stored as numbers by Excel - eg 23/02/11 is actually 40597 - so you can treat them as numbers in formulas and use >= etc.)

 

[abidraza]

you use a DSUM formula in 2003
<TABLE class=collapse><TBODY><TR class=trbgeven><TH class=thhead vAlign=top align=left>A</TH><TH class=thhead vAlign=top align=left>B</TH><TH class=thhead vAlign=top align=left>C</TH><TH class=thhead vAlign=top align=left>D</TH><TH class=thhead vAlign=top align=left>E</TH><TH class=thhead vAlign=top align=left>F</TH></TR><TR class=trbgodd><TH class=thhead vAlign=top align=left>Tree</TH><TH class=thhead vAlign=top align=left>Height</TH><TH class=thhead vAlign=top align=left>Age</TH><TH class=thhead vAlign=top align=left>Yield</TH><TH class=thhead vAlign=top align=left>Profit</TH><TH class=thhead vAlign=top align=left>Height</TH></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>="=Apple"</TD><TD class=noborder vAlign=top align=left>>10</TD><TD class=noborder vAlign=top align=left></TD><TD class=noborder vAlign=top align=left></TD><TD class=noborder vAlign=top align=left></TD><TD class=noborder vAlign=top align=left><16</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>="=Pear"</TD><TD class=noborder vAlign=top align=left></TD><TD class=noborder vAlign=top align=left></TD><TD class=noborder vAlign=top align=left></TD><TD class=noborder vAlign=top align=left></TD><TD class=noborder vAlign=top align=left></TD></TR><TR class=trbgeven><TH class=thhead vAlign=top align=left>Tree</TH><TH class=thhead vAlign=top align=left>Height</TH><TH class=thhead vAlign=top align=left>Age</TH><TH class=thhead vAlign=top align=left>Yield</TH><TH class=thhead vAlign=top align=left>Profit</TH></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>Apple</TD><TD class=noborder vAlign=top align=left>18</TD><TD class=noborder vAlign=top align=left>20</TD><TD class=noborder vAlign=top align=left>14</TD><TD class=noborder vAlign=top align=left>105.00</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>Pear</TD><TD class=noborder vAlign=top align=left>12</TD><TD class=noborder vAlign=top align=left>12</TD><TD class=noborder vAlign=top align=left>10</TD><TD class=noborder vAlign=top align=left>96.00</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>Cherry</TD><TD class=noborder vAlign=top align=left>13</TD><TD class=noborder vAlign=top align=left>14</TD><TD class=noborder vAlign=top align=left>9</TD><TD class=noborder vAlign=top align=left>105.00</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>Apple</TD><TD class=noborder vAlign=top align=left>14</TD><TD class=noborder vAlign=top align=left>15</TD><TD class=noborder vAlign=top align=left>10</TD><TD class=noborder vAlign=top align=left>75.00</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>Pear</TD><TD class=noborder vAlign=top align=left>9</TD><TD class=noborder vAlign=top align=left>8</TD><TD class=noborder vAlign=top align=left>8</TD><TD class=noborder vAlign=top align=left>76.80</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>Apple</TD><TD class=noborder vAlign=top align=left>8</TD><TD class=noborder vAlign=top align=left>9</TD><TD class=noborder vAlign=top align=left>6</TD><TD class=noborder vAlign=top align=left>45.00</TD></TR><TR class=trbgodd><TH class=thhead vAlign=top align=left>Formula</TH><TH class=thhead vAlign=top align=left>Description (Result)</TH></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>=DCOUNT(A4:E10,"Age",A1:F2)</TD><TD class=noborder vAlign=top align=left>This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers. (1)</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>=DCOUNTA(A4:E10,"Profit",A1:F2)</TD><TD class=noborder vAlign=top align=left>This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Profit fields in those records are not blank. (1)</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>=DMAX(A4:E10,"Profit",A1:A3)</TD><TD class=noborder vAlign=top align=left>The maximum profit of apple and pear trees. (105)</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>=DMIN(A4:E10,"Profit",A1:B2)</TD><TD class=noborder vAlign=top align=left>The minimum profit of apple trees over 10 in height. (75)</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>=DSUM(A4:E10,"Profit",A1:A2)</TD><TD class=noborder vAlign=top align=left>The total profit from apple trees. (225)</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>=DSUM(A4:E10,"Profit",A1:F2)</TD><TD class=noborder vAlign=top align=left>The total profit from apple trees with a height between 10 and 16. (75)</TD></TR></TBODY></TABLE>

 

[cornflakegirl]

You can use DSUM - the advantage of SUMPRODUCT is that it's just one formula in one cell, and doesn't require the slightly more complicated set up of DSUM.