SUMPRODUCT trouble with number formattin

  • Thread starter Thread starter Kristin
  • Start date Start date
K

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

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


--
 
Back
Top