SUMPRODUCT trouble with number formattin
 

I am trying to use SUMPRODUCt to count the number of occurance within 3
different criteria. Everything has been working great until I want one of
the arrays to include a column that is formatted for dollars and numbers. If
I individually change the dollars and numbers to text the formula works but
this is extremely time consuming and really a big waste of time. I have
tried selecting the entire column and adjusting the formatting but that
doesn't work...only changing each cell individually works. Please help!
--
kristin

SUMPRODUCT trouble with number formattin
 

Hi Kristin,

Show us the formula. If the number are entered as numbers and you test for
numbers then things should work. So we need to see what you are entering as
a formula.
--
Thanks,
Shane Devenshire


"Kristin" wrote:

I am trying to use SUMPRODUCt to count the number of occurance within 3
different criteria. Everything has been working great until I want one of
the arrays to include a column that is formatted for dollars and numbers. If
I individually change the dollars and numbers to text the formula works but
this is extremely time consuming and really a big waste of time. I have
tried selecting the entire column and adjusting the formatting but that
doesn't work...only changing each cell individually works. Please help!
--
kristin

SUMPRODUCT trouble with number formattin
 

Here is the formula:
=SUMPRODUCT((Data!K4:K500="AMER")*1,(Data!Y4:Y500< ="100000")*1)

I have been using a variation of it throughout my other analysis but the Y
column is formatted for $. Right now, the formula is only returning the
value for the K colum.
--
kristin


"ShaneDevenshire" wrote:

Hi Kristin,

Show us the formula. If the number are entered as numbers and you test for
numbers then things should work. So we need to see what you are entering as
a formula.
--
Thanks,
Shane Devenshire


"Kristin" wrote:

I am trying to use SUMPRODUCt to count the number of occurance within 3
different criteria. Everything has been working great until I want one of
the arrays to include a column that is formatted for dollars and numbers. If
I individually change the dollars and numbers to text the formula works but
this is extremely time consuming and really a big waste of time. I have
tried selecting the entire column and adjusting the formatting but that
doesn't work...only changing each cell individually works. Please help!
--
kristin

SUMPRODUCT trouble with number formattin
 

You're trying to get values that are greater than or equal to "100000" which
is text. You need to remove the quotes.
Try this:

=SUMPRODUCT(--(Data!K4:K500="AMER"),--(Data!Y4:Y500<=100000))

HTH,
Paul

--

"Kristin" wrote in message
...
Here is the formula:
=SUMPRODUCT((Data!K4:K500="AMER")*1,(Data!Y4:Y500< ="100000")*1)

I have been using a variation of it throughout my other analysis but the Y
column is formatted for $. Right now, the formula is only returning the
value for the K colum.
--
kristin


"ShaneDevenshire" wrote:

Hi Kristin,

Show us the formula. If the number are entered as numbers and you test
for
numbers then things should work. So we need to see what you are entering
as
a formula.
--
Thanks,
Shane Devenshire


"Kristin" wrote:

I am trying to use SUMPRODUCt to count the number of occurance within 3
different criteria. Everything has been working great until I want one
of
the arrays to include a column that is formatted for dollars and
numbers. If
I individually change the dollars and numbers to text the formula works
but
this is extremely time consuming and really a big waste of time. I
have
tried selecting the entire column and adjusting the formatting but that
doesn't work...only changing each cell individually works. Please
help!
--
kristin

SUMPRODUCT trouble with number formattin
 

I have tried ... adjusting the formatting

As long as you haven't reformatted as TEXT and then back to some other
format...

Remove the quotes from around the number:

=SUMPRODUCT((Data!K4:K500="AMER")*(Data!Y4:Y500<=1 00000))

Or:

=SUMPRODUCT(--(Data!K4:K500="AMER"),--(Data!Y4:Y500<=100000))



--
Biff
Microsoft Excel MVP


"Kristin" wrote in message
...
Here is the formula:
=SUMPRODUCT((Data!K4:K500="AMER")*1,(Data!Y4:Y500< ="100000")*1)

I have been using a variation of it throughout my other analysis but the Y
column is formatted for $. Right now, the formula is only returning the
value for the K colum.
--
kristin


"ShaneDevenshire" wrote:

Hi Kristin,

Show us the formula. If the number are entered as numbers and you test
for
numbers then things should work. So we need to see what you are entering
as
a formula.
--
Thanks,
Shane Devenshire


"Kristin" wrote:

I am trying to use SUMPRODUCt to count the number of occurance within 3
different criteria. Everything has been working great until I want one
of
the arrays to include a column that is formatted for dollars and
numbers. If
I individually change the dollars and numbers to text the formula works
but
this is extremely time consuming and really a big waste of time. I
have
tried selecting the entire column and adjusting the formatting but that
doesn't work...only changing each cell individually works. Please
help!
--
kristin

SUMPRODUCT trouble with number formattin
 

THANKS! I knew it was something easy just couldn't see it. Here is another
one for you.

This formula works: =SUMPRODUCT(('Lease Data'!B2:B498="AMER")*1,('Lease
Data'!E2:E498<="2006")*1)

This formula returns a zero value (which it shouldn't): =SUMIF('Lease
Data'!E:E,"<2006",'Lease Data'!C:C)



--
kristin


"PCLIVE" wrote:

You're trying to get values that are greater than or equal to "100000" which
is text. You need to remove the quotes.
Try this:

=SUMPRODUCT(--(Data!K4:K500="AMER"),--(Data!Y4:Y500<=100000))

HTH,
Paul

--

"Kristin" wrote in message
...
Here is the formula:
=SUMPRODUCT((Data!K4:K500="AMER")*1,(Data!Y4:Y500< ="100000")*1)

I have been using a variation of it throughout my other analysis but the Y
column is formatted for $. Right now, the formula is only returning the
value for the K colum.
--
kristin


"ShaneDevenshire" wrote:

Hi Kristin,

Show us the formula. If the number are entered as numbers and you test
for
numbers then things should work. So we need to see what you are entering
as
a formula.
--
Thanks,
Shane Devenshire


"Kristin" wrote:

I am trying to use SUMPRODUCt to count the number of occurance within 3
different criteria. Everything has been working great until I want one
of
the arrays to include a column that is formatted for dollars and
numbers. If
I individually change the dollars and numbers to text the formula works
but
this is extremely time consuming and really a big waste of time. I
have
tried selecting the entire column and adjusting the formatting but that
doesn't work...only changing each cell individually works. Please
help!
--
kristin

SUMPRODUCT trouble with number formattin
 

Are any of the values in column E:E "less than" 2006. The formula works
for me.

Also, your other formula seems a bit unorthodox.
You can do it a couple of different ways:
=SUMPRODUCT(('Lease Data'!B2:B498="AMER")*('Lease Data'!E2:E498<="2006"))
or
=SUMPRODUCT(--('Lease Data'!B2:B498="AMER"),--('Lease
Data'!E2:E498<="2006"))

Regards,
Paul


--

"Kristin" wrote in message
...
THANKS! I knew it was something easy just couldn't see it. Here is
another
one for you.

This formula works: =SUMPRODUCT(('Lease Data'!B2:B498="AMER")*1,('Lease
Data'!E2:E498<="2006")*1)

This formula returns a zero value (which it shouldn't): =SUMIF('Lease
Data'!E:E,"<2006",'Lease Data'!C:C)



--
kristin


"PCLIVE" wrote:

You're trying to get values that are greater than or equal to "100000"
which
is text. You need to remove the quotes.
Try this:

=SUMPRODUCT(--(Data!K4:K500="AMER"),--(Data!Y4:Y500<=100000))

HTH,
Paul

--

"Kristin" wrote in message
...
Here is the formula:
=SUMPRODUCT((Data!K4:K500="AMER")*1,(Data!Y4:Y500< ="100000")*1)

I have been using a variation of it throughout my other analysis but
the Y
column is formatted for $. Right now, the formula is only returning
the
value for the K colum.
--
kristin


"ShaneDevenshire" wrote:

Hi Kristin,

Show us the formula. If the number are entered as numbers and you
test
for
numbers then things should work. So we need to see what you are
entering
as
a formula.
--
Thanks,
Shane Devenshire


"Kristin" wrote:

I am trying to use SUMPRODUCt to count the number of occurance
within 3
different criteria. Everything has been working great until I want
one
of
the arrays to include a column that is formatted for dollars and
numbers. If
I individually change the dollars and numbers to text the formula
works
but
this is extremely time consuming and really a big waste of time. I
have
tried selecting the entire column and adjusting the formatting but
that
doesn't work...only changing each cell individually works. Please
help!
--
kristin