Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi
Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#2
![]() |
|||
|
|||
![]()
Hi FlamencoKid,
If the condition is that a value in column [C] is greater than or equal to 100,000 and 199,999 at the same time, then the only values that would qualify would be greater or equal to 199,999 Is that what you are after? Regards, KL "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#3
![]() |
|||
|
|||
![]()
Change:
$C$2:$C$5000=199999 TO: $C$2:$C$5000<=199999 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#4
![]() |
|||
|
|||
![]()
Sorry, typo on my part! Should have been <=199999 and it still doesn't work :)
Any thoughts? "Ragdyer" wrote: Change: $C$2:$C$5000=199999 TO: $C$2:$C$5000<=199999 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#5
![]() |
|||
|
|||
![]()
Then the only reasonable explanation I see is that your values are in
reality text strings and not numeric values. As a test try the following formula and if it works then you know where the issue is: =SUMPRODUCT((--Data!$C$2:$C$5000=100000)*(--Data!$C$2:$C$5000<=199999)*(--Data!$E$2:$E$5000)) Regards, KL "FlamencoKid" wrote in message ... Sorry, typo on my part! Should have been <=199999 and it still doesn't work :) Any thoughts? "Ragdyer" wrote: Change: $C$2:$C$5000=199999 TO: $C$2:$C$5000<=199999 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#6
![]() |
|||
|
|||
![]()
Hi
Tried your suggestion and the result was #VALUE! Does this mean the source data was text? If so, should I Edit Paste Special and turn it to a value? Thanks a lot for your help "KL" wrote: Then the only reasonable explanation I see is that your values are in reality text strings and not numeric values. As a test try the following formula and if it works then you know where the issue is: =SUMPRODUCT((--Data!$C$2:$C$5000=100000)*(--Data!$C$2:$C$5000<=199999)*(--Data!$E$2:$E$5000)) Regards, KL "FlamencoKid" wrote in message ... Sorry, typo on my part! Should have been <=199999 and it still doesn't work :) Any thoughts? "Ragdyer" wrote: Change: $C$2:$C$5000=199999 TO: $C$2:$C$5000<=199999 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#7
![]() |
|||
|
|||
![]()
Hi FlamencoKid,
This probably suggests that some of the ranges used contain text values that can not be forced into a number. I would rather try DataText to Columns... etc. Regards, KL "FlamencoKid" wrote in message ... Hi Tried your suggestion and the result was #VALUE! Does this mean the source data was text? If so, should I Edit Paste Special and turn it to a value? Thanks a lot for your help "KL" wrote: Then the only reasonable explanation I see is that your values are in reality text strings and not numeric values. As a test try the following formula and if it works then you know where the issue is: =SUMPRODUCT((--Data!$C$2:$C$5000=100000)*(--Data!$C$2:$C$5000<=199999)*(--Data!$E$2:$E$5000)) Regards, KL "FlamencoKid" wrote in message ... Sorry, typo on my part! Should have been <=199999 and it still doesn't work :) Any thoughts? "Ragdyer" wrote: Change: $C$2:$C$5000=199999 TO: $C$2:$C$5000<=199999 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#8
![]() |
|||
|
|||
![]()
Sumproduct has the advantage of performing as an array function *without*
actually being an array formula. On the other hand, it *only* works when the return is numerical. Now, you used the asterisk form of Sumproduct, which I do prefer, because it warns you if your data is contaminated (contains alpha text) with an error message (#VALUE!). Since you have *not* received an error message, that means that the data in Column E *is* numerical. Notice, I didn't say text, because with the asterisk form of Sumproduct, text numbers *are* properly evaluated along with real numbers. So that leaves Column C as your problem data. Do you import your data? If you do, that opens up a whole other can of worms. Try this formula and post back with the results: =SUMPRODUCT((Data!$C$2:$C$5000="100000")*(Data!$C $2:$C$5000<="199999")*(Dat a!$E$2:$E$5000)) -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "FlamencoKid" wrote in message ... Hi Tried your suggestion and the result was #VALUE! Does this mean the source data was text? If so, should I Edit Paste Special and turn it to a value? Thanks a lot for your help "KL" wrote: Then the only reasonable explanation I see is that your values are in reality text strings and not numeric values. As a test try the following formula and if it works then you know where the issue is: =SUMPRODUCT((--Data!$C$2:$C$5000=100000)*(--Data!$C$2:$C$5000<=199999)*(--D ata!$E$2:$E$5000)) Regards, KL "FlamencoKid" wrote in message ... Sorry, typo on my part! Should have been <=199999 and it still doesn't work :) Any thoughts? "Ragdyer" wrote: Change: $C$2:$C$5000=199999 TO: $C$2:$C$5000<=199999 -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E $2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
#9
![]() |
|||
|
|||
![]()
Hi
Yeah, I'd imported the codes (and some other data I was using) and it looks like they were treated as text. As soon as I resolved that it sorted the problem. Thanks very much for your help and sorry for the typo at the start of all this that complicated matters! "KL" wrote: Hi FlamencoKid, This probably suggests that some of the ranges used contain text values that can not be forced into a number. I would rather try DataText to Columns... etc. Regards, KL "FlamencoKid" wrote in message ... Hi Tried your suggestion and the result was #VALUE! Does this mean the source data was text? If so, should I Edit Paste Special and turn it to a value? Thanks a lot for your help "KL" wrote: Then the only reasonable explanation I see is that your values are in reality text strings and not numeric values. As a test try the following formula and if it works then you know where the issue is: =SUMPRODUCT((--Data!$C$2:$C$5000=100000)*(--Data!$C$2:$C$5000<=199999)*(--Data!$E$2:$E$5000)) Regards, KL "FlamencoKid" wrote in message ... Sorry, typo on my part! Should have been <=199999 and it still doesn't work :) Any thoughts? "Ragdyer" wrote: Change: $C$2:$C$5000=199999 TO: $C$2:$C$5000<=199999 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FlamencoKid" wrote in message ... Hi Values for criteria that I'm looking at are in column C, values to sum are in column E. I'm trying to add together (not count) all the amounts in column E that have corresponding values in C between two numbers. The following formula produces 0 when I know (I can see) that there are rows that match the criteria. The numbers in column C are codes for products by the way and they won't necessarily be consecutive. =sumproduct((Data!$C$2:$C$5000=100000)*(Data!$C$2 :$C$5000=199999)*(Data!$E$2:$E$5000)) Any help greatly appreciated - this is driving me nuts! I swear I got it working at one stage but now it no longer seems to work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Group values | Excel Worksheet Functions | |||
Combine FREQUENCY and SUM of Associated Values | Excel Worksheet Functions | |||
searching for values and summing the corresponding values | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |