| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with date range
Hi,
I want to count an "A" column where the column "B" is between a given date. The date in column B has a date and a time("dd-mm-yyyy hh:mm:ss"). The date arguments in the sumproduct are in the "dd-mm-yyyy" form. The formula should find "like products", not the exact match. Thank you, ermeko |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with date range
=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01"
),--(B2:B200<=--"2006-08-31")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Hi, I want to count an "A" column where the column "B" is between a given date. The date in column B has a date and a time("dd-mm-yyyy hh:mm:ss"). The date arguments in the sumproduct are in the "dd-mm-yyyy" form. The formula should find "like products", not the exact match. Thank you, ermeko |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with date range
Thanks,
=SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-01")) gives zero but there are dates like 2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column. When the begining and ending date is the same it does nor sum the quantity. "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-31")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Hi, I want to count an "A" column where the column "B" is between a given date. The date in column B has a date and a time("dd-mm-yyyy hh:mm:ss"). The date arguments in the sumproduct are in the "dd-mm-yyyy" form. The formula should find "like products", not the exact match. Thank you, ermeko |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with date range
what is the product name and what value are you testing for (My formula was
just an example). -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Thanks, =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-01")) gives zero but there are dates like 2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column. When the begining and ending date is the same it does nor sum the quantity. "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-31")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Hi, I want to count an "A" column where the column "B" is between a given date. The date in column B has a date and a time("dd-mm-yyyy hh:mm:ss"). The date arguments in the sumproduct are in the "dd-mm-yyyy" form. The formula should find "like products", not the exact match. Thank you, ermeko |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with date range
Hi,
the formula is a bit different: =SUMPRODUCT(--(b2:B200=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(c2:c200="apple"),a2:a200) C is a column with product names. when I change formula to: =SUMPRODUCT(--(b2:B200=--"2006-08-01"),--(B2:B200<=--"2006-08-03"),--(B2:B200<=--"2006-08-01"),--(c2:c200="apple"),a2:a200) it works but it but when I want the sum for only one day it does not work. Thank you "Bob Phillips" wrote: what is the product name and what value are you testing for (My formula was just an example). -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Thanks, =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-01")) gives zero but there are dates like 2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column. When the begining and ending date is the same it does nor sum the quantity. "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-31")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Hi, I want to count an "A" column where the column "B" is between a given date. The date in column B has a date and a time("dd-mm-yyyy hh:mm:ss"). The date arguments in the sumproduct are in the "dd-mm-yyyy" form. The formula should find "like products", not the exact match. Thank you, ermeko |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with date range
=SUMPRODUCT(--(b2:B200=--"2006-08-01"),--(B2:B200<=--"2006-08-03"),--(B2:B200<=--"2006-08-01"),--(c2:c200="apple"),a2:a200) it works but it but when I want the sum for only one day it does not work. I am a little confused, what do the 2nd and 3rd term do? if b2:b200 < 2006-08-01 it will also have to be less than 2006-08-03 Also as it is written it will only select values in column B if they equal 2006-08-01. If any times are also included for a given day, it will not be selected, would would have to add 1 to the day you were interssted in to account for this. or am I missing something Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=571351 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with date range
You don't need to test <= twice.
This works for me with just a single day =SUMPRODUCT(--(B2:B200=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(C2:C2 00="apple"),A2:A200) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Hi, the formula is a bit different: =SUMPRODUCT(--(b2:B200=--"2006-08-01"),--(B2:B200<=--"2006-08-01"),--(B2:B2 00<=--"2006-08-01"),--(c2:c200="apple"),a2:a200) C is a column with product names. when I change formula to: =SUMPRODUCT(--(b2:B200=--"2006-08-01"),--(B2:B200<=--"2006-08-03"),--(B2:B2 00<=--"2006-08-01"),--(c2:c200="apple"),a2:a200) it works but it but when I want the sum for only one day it does not work. Thank you "Bob Phillips" wrote: what is the product name and what value are you testing for (My formula was just an example). -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Thanks, =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-01")) gives zero but there are dates like 2006-08-01 23:02:00, 2006-08-01 22:00:00 in B column. When the begining and ending date is the same it does nor sum the quantity. "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("product",A2:A200))),--(b2:B200=--"2006-08-01" ),--(B2:B200<=--"2006-08-31")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ermeko" wrote in message ... Hi, I want to count an "A" column where the column "B" is between a given date. The date in column B has a date and a time("dd-mm-yyyy hh:mm:ss"). The date arguments in the sumproduct are in the "dd-mm-yyyy" form. The formula should find "like products", not the exact match. Thank you, ermeko |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return info based on Date Range | Excel Worksheet Functions | |||
| Advanced filter a dynamic date range | Excel Worksheet Functions | |||
| Crate group of date, with Dynamic Range in pivot table not working | Excel Discussion (Misc queries) | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
| Date range criteria and Pivot tables (again!) | Excel Worksheet Functions | |||
Linear Mode
