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