FUNCTION arguments

  • Thread starter Thread starter mbr
  • Start date Start date
M

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.
 
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
 
mbr said:
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.
Click to expand...

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

If this post helps click Yes
 
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
 
Back
Top