Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in Formula not working
Please help! Can't figure out why this isn't working!
I'm trying to count the occurences of a date in a column, along with another value in another column (product name). My formula is as follows: = sum(if(range="productname",if(range=1/03/2006,1,0))) I'm getting a value of "0" for the answer. If I replace the date above with another value in another column (text), it appears with the correct answer. But the correct answer is only appearing in the formula builder (= sign at the top left of the page), but it still shows a 0 in the result cell. So I guess the problem is twofold. Any help would be appreciated! -- Dana |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in Formula not working
I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3)))
(You didn't indicate what your ranges are. One limitation of sumproduct is that you can't use entire columns such as A:A; you have to include row qualifiers, even if they select the entire column: A1:A65536). --Bruce "Dana" wrote: Please help! Can't figure out why this isn't working! I'm trying to count the occurences of a date in a column, along with another value in another column (product name). My formula is as follows: = sum(if(range="productname",if(range=1/03/2006,1,0))) I'm getting a value of "0" for the answer. If I replace the date above with another value in another column (text), it appears with the correct answer. But the correct answer is only appearing in the formula builder (= sign at the top left of the page), but it still shows a 0 in the result cell. So I guess the problem is twofold. Any help would be appreciated! -- Dana |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in Formula not working
Makes sense, but it didn't work. Still getting 0 as the result.
My formula is: =sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3))) -- Dana "bpeltzer" wrote: I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3))) (You didn't indicate what your ranges are. One limitation of sumproduct is that you can't use entire columns such as A:A; you have to include row qualifiers, even if they select the entire column: A1:A65536). --Bruce "Dana" wrote: Please help! Can't figure out why this isn't working! I'm trying to count the occurences of a date in a column, along with another value in another column (product name). My formula is as follows: = sum(if(range="productname",if(range=1/03/2006,1,0))) I'm getting a value of "0" for the answer. If I replace the date above with another value in another column (text), it appears with the correct answer. But the correct answer is only appearing in the formula builder (= sign at the top left of the page), but it still shows a 0 in the result cell. So I guess the problem is twofold. Any help would be appreciated! -- Dana |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in Formula not working
You may want to copy the formula directly from the formula bar and post into the
message. I would bet that "productname" doesn't appear in b4:B279 of worksheet1 or you don't have any January 1, 2006 in L4:L279 (or you don't really have dates in L4:L279--maybe it's just text that looks like a date). Dana wrote: Makes sense, but it didn't work. Still getting 0 as the result. My formula is: =sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3))) -- Dana "bpeltzer" wrote: I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3))) (You didn't indicate what your ranges are. One limitation of sumproduct is that you can't use entire columns such as A:A; you have to include row qualifiers, even if they select the entire column: A1:A65536). --Bruce "Dana" wrote: Please help! Can't figure out why this isn't working! I'm trying to count the occurences of a date in a column, along with another value in another column (product name). My formula is as follows: = sum(if(range="productname",if(range=1/03/2006,1,0))) I'm getting a value of "0" for the answer. If I replace the date above with another value in another column (text), it appears with the correct answer. But the correct answer is only appearing in the formula builder (= sign at the top left of the page), but it still shows a 0 in the result cell. So I guess the problem is twofold. Any help would be appreciated! -- Dana -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in Formula not working
The formula works when I use anything but the dates. If I replace the date
and use another column with text, it works! When my cursor goes over the cell with the first date in it (L4), the formula bar reads "1/3/2006 6:34:00 PM". It's exported data from a CRM tool. When I click on "Format - Cells", it shows it as a "date" in the "number" tab. -- Dana "Dave Peterson" wrote: You may want to copy the formula directly from the formula bar and post into the message. I would bet that "productname" doesn't appear in b4:B279 of worksheet1 or you don't have any January 1, 2006 in L4:L279 (or you don't really have dates in L4:L279--maybe it's just text that looks like a date). Dana wrote: Makes sense, but it didn't work. Still getting 0 as the result. My formula is: =sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3))) -- Dana "bpeltzer" wrote: I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3))) (You didn't indicate what your ranges are. One limitation of sumproduct is that you can't use entire columns such as A:A; you have to include row qualifiers, even if they select the entire column: A1:A65536). --Bruce "Dana" wrote: Please help! Can't figure out why this isn't working! I'm trying to count the occurences of a date in a column, along with another value in another column (product name). My formula is as follows: = sum(if(range="productname",if(range=1/03/2006,1,0))) I'm getting a value of "0" for the answer. If I replace the date above with another value in another column (text), it appears with the correct answer. But the correct answer is only appearing in the formula builder (= sign at the top left of the page), but it still shows a 0 in the result cell. So I guess the problem is twofold. Any help would be appreciated! -- Dana -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date in Formula not working
Try
=SUMPRODUCT(--(worksheet1!B4:B279="productname"),--(INT(worksheet1!L4:L279)=DATE(2006,1,3))) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dana" wrote in message ... The formula works when I use anything but the dates. If I replace the date and use another column with text, it works! When my cursor goes over the cell with the first date in it (L4), the formula bar reads "1/3/2006 6:34:00 PM". It's exported data from a CRM tool. When I click on "Format - Cells", it shows it as a "date" in the "number" tab. -- Dana "Dave Peterson" wrote: You may want to copy the formula directly from the formula bar and post into the message. I would bet that "productname" doesn't appear in b4:B279 of worksheet1 or you don't have any January 1, 2006 in L4:L279 (or you don't really have dates in L4:L279--maybe it's just text that looks like a date). Dana wrote: Makes sense, but it didn't work. Still getting 0 as the result. My formula is: =sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3))) -- Dana "bpeltzer" wrote: I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3))) (You didn't indicate what your ranges are. One limitation of sumproduct is that you can't use entire columns such as A:A; you have to include row qualifiers, even if they select the entire column: A1:A65536). --Bruce "Dana" wrote: Please help! Can't figure out why this isn't working! I'm trying to count the occurences of a date in a column, along with another value in another column (product name). My formula is as follows: = sum(if(range="productname",if(range=1/03/2006,1,0))) I'm getting a value of "0" for the answer. If I replace the date above with another value in another column (text), it appears with the correct answer. But the correct answer is only appearing in the formula builder (= sign at the top left of the page), but it still shows a 0 in the result cell. So I guess the problem is twofold. Any help would be appreciated! -- Dana -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
date that updates itself daily, plus another formula please?? | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Value between 2 dates | Excel Worksheet Functions | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions |