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.

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
---------------
Jacob Skaria


"mbr" wrote:

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.

"mbr" wrote:
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), D1:D100)

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.

One way....

Use cells to hold your date boundaries:

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

=SUMIF(B1:B10,"="&F1,D1:D10)-SUMIF(B1:B10,""&G1,D1:D10)

--
Biff
Microsoft Excel MVP


"mbr" wrote in message
...
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.

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" wrote:

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
---------------
Jacob Skaria


"mbr" wrote:

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.

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",D1:D100))

If you want to ignore blanks and zeroes try the below version
=AVERAGE(IF(TEXT(B1:B100,"mm")="07",IF(D1:D1000,D 1:D100)))

If this post helps click Yes
---------------
Jacob Skaria


"mbr" wrote:

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" wrote:

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
---------------
Jacob Skaria


"mbr" wrote:

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.

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"),D2:D10))
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
---
"mbr" wrote:
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?