FUNCTION arguments

[mbr]

what function would i need to do the following:

add amounts in column d if column b is between 01/07/09 and 31/07/09.

 

[Jacob Skaria]

The below function will return 10 if the month of date in B2 is 7.

=IF(--TEXT(B2,"mm")=7,10,0)

=IF(--TEXT(B2,"mm")=7,"True","False")


If this post helps click Yes

 

[JoeU2004]

what function would i need to do the following:
add amounts in column d if column b is between 01/07/09 and 31/07/09.

It would be best to put the dates into cells, say A1 and B1. Then:

=sumproduct((A1<=B1:B100)*(B1:B100<=B1), D1100)

If you do not want to put the dates into cells for some reason, replace A1
and B1 above with DATE(2009,7,1) and DATE(2009,7,31) respectively.

 

[T. Valko]

One way....

Use cells to hold your date boundaries:

F1 = 01/07/09
G1 = 31/07/09

=SUMIF(B1:B10,">="&F1,D110)-SUMIF(B1:B10,">"&G1,D110)

 

[mbr]

i need to be able to capture the average of amounts in column d if column b
is between 1/7/9 and 31/7/9. can it be done?

 

[Jacob Skaria]

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=AVERAGE(IF(TEXT(B1:B100,"mm")="07",D1100))

If you want to ignore blanks and zeroes try the below version
=AVERAGE(IF(TEXT(B1:B100,"mm")="07",IF(D1100>0,D1100)))

If this post helps click Yes

 

[Max]

For any particular mth/yr, you could capture it unambiguously, like this
In say, E2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=AVERAGE(IF(--(TEXT(B2:B10,"mmmyy")="Jul09"),D210))
Above presumes dates in col B are real dates
Adapt to suit the actual extents of your data

Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik