SUMPRODUCT trouble with number formattin

[Kristin]

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!

 

[ShaneDevenshire]

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.

 

[Kristin]

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.

 

[PCLIVE]

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

--

 

[T. Valko]

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<=100000))

Or:

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

 

[Kristin]

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)

 

[PCLIVE]

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


--