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

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

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

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

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

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

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

Thanks also to Dave and Bruce!!!
--
Dana


"Peo Sjoblom" wrote:

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

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


Dana wrote:

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

Thanks also to Dave and Bruce!!!
--
Dana

"Peo Sjoblom" wrote:

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




--

Dave Peterson

Thanks again! This was extremely helpful!

--
Dana


"Dave Peterson" wrote:

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


Dana wrote:

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

Thanks also to Dave and Bruce!!!
--
Dana

"Peo Sjoblom" wrote:

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




--

Dave Peterson

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" wrote:

Thanks again! This was extremely helpful!

--
Dana


"Dave Peterson" wrote:

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


Dana wrote:

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

Thanks also to Dave and Bruce!!!
--
Dana

"Peo Sjoblom" wrote:

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




--

Dave Peterson

How about:

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



Dana wrote:

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" wrote:

Thanks again! This was extremely helpful!

--
Dana


"Dave Peterson" wrote:

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


Dana wrote:

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

Thanks also to Dave and Bruce!!!
--
Dana

"Peo Sjoblom" wrote:

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




--

Dave Peterson


--

Dave Peterson

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" wrote:

How about:

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



Dana wrote:

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" wrote:

Thanks again! This was extremely helpful!

--
Dana


"Dave Peterson" wrote:

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


Dana wrote:

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

Thanks also to Dave and Bruce!!!
--
Dana

"Peo Sjoblom" wrote:

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




--

Dave Peterson


--

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Dana wrote:

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" wrote:

How about:

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



Dana wrote:

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" wrote:

Thanks again! This was extremely helpful!

--
Dana


"Dave Peterson" wrote:

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


Dana wrote:

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

Thanks also to Dave and Bruce!!!
--
Dana

"Peo Sjoblom" wrote:

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




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson