Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
I can't seem to make SUMPRODUCT work with a date field as a criteria.
SUMPRODUCT(range="x")*(range="9/1/07") I would like for the date range to look for "*/5/*" ie. amy month, any year. If I change the range and look for words in that range it works. I just can't seem to get it to work with a date. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
Try this:
=SUMPRODUCT((range="x")*(MONTH(range)=5)) Don't forget the parentheses!! Assumes dates are in Excel date format. Hope this helps. Pete On Oct 9, 12:14 am, Coley wrote: I can't seem to make SUMPRODUCT work with a date field as a criteria. SUMPRODUCT(range="x")*(range="9/1/07") I would like for the date range to look for "*/5/*" ie. amy month, any year. If I change the range and look for words in that range it works. I just can't seem to get it to work with a date. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
.. date range to look for "*/5/*" ie. any month, any year
One way is to use TEXT on the dates col With real dates assumed in col B, you could try something like this in say, C2: =SUMPRODUCT((A2:A100="x")*(TEXT(B2:B100,"d")="5")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Coley" wrote: I can't seem to make SUMPRODUCT work with a date field as a criteria. SUMPRODUCT(range="x")*(range="9/1/07") I would like for the date range to look for "*/5/*" ie. amy month, any year. If I change the range and look for words in that range it works. I just can't seem to get it to work with a date. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
or just
=SUMPRODUCT((A2:A100="x")*(DAY(B2:B100)=5)) "Max" wrote: .. date range to look for "*/5/*" ie. any month, any year One way is to use TEXT on the dates col With real dates assumed in col B, you could try something like this in say, C2: =SUMPRODUCT((A2:A100="x")*(TEXT(B2:B100,"d")="5")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Coley" wrote: I can't seem to make SUMPRODUCT work with a date field as a criteria. SUMPRODUCT(range="x")*(range="9/1/07") I would like for the date range to look for "*/5/*" ie. amy month, any year. If I change the range and look for words in that range it works. I just can't seem to get it to work with a date. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
Sorry, I mis-read what you wanted, as in the UK the middle part of the
date is the month. If you want the fifth day then change MONTH to DAY in my formula. Pete I can't seem to make SUMPRODUCT work with a date field as a criteria. SUMPRODUCT(range="x")*(range="9/1/07") I would like for the date range to look for "*/5/*" ie. amy month, any year. If I change the range and look for words in that range it works. I just can't seem to get it to work with a date.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SUMPRODUCT? | Excel Worksheet Functions | |||
using -- with SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct | Excel Discussion (Misc queries) | |||
Help with SUMPRODUCT | Excel Discussion (Misc queries) |