Date in Formula not working

D

Dana

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!
 
B

bpeltzer

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
 
D

Dana

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)))
 
D

Dave Peterson

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).
 
D

Dana

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.
 
P

Peo Sjoblom

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

Dana

This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?

Thanks also to Dave and Bruce!!!
 
D

Dave Peterson

=int() returns the whole part of the number. It ignores the fraction.

=int(3.5) = 3

With dates/times, days are whole numbers and times are fractions.

This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?

Thanks also to Dave and Bruce!!!
Click to expand...
 
D

Dana

How would I use this same formula if I wanted to use an "or" statement with
multiple product names, i.e., count the number of times "product 1" or
"product 2" or "product 3" appears in range "worksheet1!B4:B279" for the date
specified in range ""--(INT(worksheet1!L4:L279)=DATE(2006,1,3)))?

The formula below only counts one product in that range.

Thanks again!

--
Dana


Dana said:
Thanks again! This was extremely helpful!
Click to expand...
 
D

Dave Peterson

How about:

=SUMPRODUCT((worksheet1!B4:B279={"product 1","product 2","product 3"})
*(INT(worksheet1!L4:L279)=DATE(2006,1,3)))
 
D

Dana

Thanks Dave! That worked!

If you have some extra time, could you explain these formulas to me? Even
though they're working and doing exactly what I need them to do, I want to
make sure I understand them.

I'm used to using "Countif" and "Sumif", but I'm not entirely familiar with
Sumproduct, particularly with the use of the double hyphens, etc. Any
explanation as to the way this formula is built would be extremely helpful.

Thanks again for all your help!! This site is fantastic, and has saved my
company a lot of time!!!
 
D

Dave Peterson

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Thanks Dave! That worked!

If you have some extra time, could you explain these formulas to me? Even
though they're working and doing exactly what I need them to do, I want to
make sure I understand them.

I'm used to using "Countif" and "Sumif", but I'm not entirely familiar with
Sumproduct, particularly with the use of the double hyphens, etc. Any
explanation as to the way this formula is built would be extremely helpful.

Thanks again for all your help!! This site is fantastic, and has saved my
company a lot of time!!!

--
Dana

Dave Peterson said:
How about:

=SUMPRODUCT((worksheet1!B4:B279={"product 1","product 2","product 3"})
*(INT(worksheet1!L4:L279)=DATE(2006,1,3)))
Click to expand...
Click to expand...
 
Top