| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct trouble | Excel Worksheet Functions | |||
| SUMPRODUCT - Giving me trouble | Excel Worksheet Functions | |||
| SUMPRODUCT trouble | Excel Worksheet Functions | |||
| Trouble with SUMPRODUCT | Excel Worksheet Functions | |||
| help please - trouble with sumproduct function | Excel Worksheet Functions | |||
Hybrid Mode
