Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP ON SUMPRODUCT() FUNCTION
Hi,
=SUMPRODUCT(--(DecMaxKms($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel(D$23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23)) I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial ac8:ac19478, and so on..). At the same time I have a range name DecAll with range ab8:af19478, to get sum of all columns with given condition, there I get error as "Value#". Can You all help me out ? many thanks in advance. When I just put = sumproduct(decall) - it is giving result, but with condition like above it is throwing error. Am I expecting more or I have done some error, please help me. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP ON SUMPRODUCT() FUNCTION
For the last one you need
=SUMPRODUCT((DecMaxKms($B26-1))*(DecMaxKms<=($C26))* (DecTruckModel(D$23-1))*(DecTruckModel<=(D$24))* (INDIRECT("DEC"&$B$23))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi, =SUMPRODUCT(--(DecMaxKms($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel(D $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23)) I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial ac8:ac19478, and so on..). At the same time I have a range name DecAll with range ab8:af19478, to get sum of all columns with given condition, there I get error as "Value#". Can You all help me out ? many thanks in advance. When I just put = sumproduct(decall) - it is giving result, but with condition like above it is throwing error. Am I expecting more or I have done some error, please help me. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP ON SUMPRODUCT() FUNCTION
for multicolumn DecAll do it this way
=SUMPRODUCT((DecMaxKms($B26-1))*(DecMaxKms<=$C26)*DecAll) -- Regards, Tom Ogilvy "Eddy Stan" wrote: Hi, =SUMPRODUCT(--(DecMaxKms($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel(D$23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23)) I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial ac8:ac19478, and so on..). At the same time I have a range name DecAll with range ab8:af19478, to get sum of all columns with given condition, there I get error as "Value#". Can You all help me out ? many thanks in advance. When I just put = sumproduct(decall) - it is giving result, but with condition like above it is throwing error. Am I expecting more or I have done some error, please help me. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP ON SUMPRODUCT() FUNCTION
No, it should work for any value. One thing to check is that all the named
ranges are the same size, SP depends upon that. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi, I copied your formula, still I get error. BUT I redefined the range name like DMax & DAll, it worked - for my multiple range sum request. But the same formula did not work to find sum for Hire, Special, Extras, Halting... separately. That is my b23 is a validation cell having list of Hire, Special, Extras & All also. Which mean my user should be able to for single as well as for all. So Do I have to check for "All" & put this formula and if not "All" put the previous formula ?? For the time being I am checking like if($b$23="All",this formula,previous formula) or do I have comman sumproduct() & I am missing something ? kindly Clarify. Thank you for the contribution. "Bob Phillips" wrote: For the last one you need =SUMPRODUCT((DecMaxKms($B26-1))*(DecMaxKms<=($C26))* (DecTruckModel(D$23-1))*(DecTruckModel<=(D$24))* (INDIRECT("DEC"&$B$23))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi, =SUMPRODUCT(--(DecMaxKms($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel(D $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23)) I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial ac8:ac19478, and so on..). At the same time I have a range name DecAll with range ab8:af19478, to get sum of all columns with given condition, there I get error as "Value#". Can You all help me out ? many thanks in advance. When I just put = sumproduct(decall) - it is giving result, but with condition like above it is throwing error. Am I expecting more or I have done some error, please help me. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP ON SUMPRODUCT() FUNCTION
Hi Bob,
Thanks for the same. I check the range sizes and they are all of same size. It is a big file 13mb (19497 rows of columns up to AW, where I use SP based on 2 columns giving upper & lower range to sum 7 columns individually & once all together. I checked the results by filter & subtotal() the result was 207K with SP it was 1086k (I thought it was correct as it didn't throw error), since the variation was huge I created 7 blocks to find individual results to find total which came exactly 207k. I was unable to find why it showed 1086k. Ordinary sum of 7 columns ae 3347k. So the SP is doing something but unable to understand the parameter or my worksheet has some confusing thing, it was just SAP exported data. Thanks again to you & Tom "Bob Phillips" wrote: No, it should work for any value. One thing to check is that all the named ranges are the same size, SP depends upon that. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi, I copied your formula, still I get error. BUT I redefined the range name like DMax & DAll, it worked - for my multiple range sum request. But the same formula did not work to find sum for Hire, Special, Extras, Halting... separately. That is my b23 is a validation cell having list of Hire, Special, Extras & All also. Which mean my user should be able to for single as well as for all. So Do I have to check for "All" & put this formula and if not "All" put the previous formula ?? For the time being I am checking like if($b$23="All",this formula,previous formula) or do I have comman sumproduct() & I am missing something ? kindly Clarify. Thank you for the contribution. "Bob Phillips" wrote: For the last one you need =SUMPRODUCT((DecMaxKms($B26-1))*(DecMaxKms<=($C26))* (DecTruckModel(D$23-1))*(DecTruckModel<=(D$24))* (INDIRECT("DEC"&$B$23))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi, =SUMPRODUCT(--(DecMaxKms($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel(D $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23)) I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial ac8:ac19478, and so on..). At the same time I have a range name DecAll with range ab8:af19478, to get sum of all columns with given condition, there I get error as "Value#". Can You all help me out ? many thanks in advance. When I just put = sumproduct(decall) - it is giving result, but with condition like above it is throwing error. Am I expecting more or I have done some error, please help me. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP ON SUMPRODUCT() FUNCTION
Eddy,
It is very difficult to see the problem without seeing the workbook. All I know is that the technique works. Perhaps you could take a copy of the workbook and remove some rows of data, and keep doing that until the results are the same as filter subtotal. Once you get there, restore the last deleted data and home in that data removing bit by bit, until you home in on the cause. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi Bob, Thanks for the same. I check the range sizes and they are all of same size. It is a big file 13mb (19497 rows of columns up to AW, where I use SP based on 2 columns giving upper & lower range to sum 7 columns individually & once all together. I checked the results by filter & subtotal() the result was 207K with SP it was 1086k (I thought it was correct as it didn't throw error), since the variation was huge I created 7 blocks to find individual results to find total which came exactly 207k. I was unable to find why it showed 1086k. Ordinary sum of 7 columns ae 3347k. So the SP is doing something but unable to understand the parameter or my worksheet has some confusing thing, it was just SAP exported data. Thanks again to you & Tom "Bob Phillips" wrote: No, it should work for any value. One thing to check is that all the named ranges are the same size, SP depends upon that. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi, I copied your formula, still I get error. BUT I redefined the range name like DMax & DAll, it worked - for my multiple range sum request. But the same formula did not work to find sum for Hire, Special, Extras, Halting... separately. That is my b23 is a validation cell having list of Hire, Special, Extras & All also. Which mean my user should be able to for single as well as for all. So Do I have to check for "All" & put this formula and if not "All" put the previous formula ?? For the time being I am checking like if($b$23="All",this formula,previous formula) or do I have comman sumproduct() & I am missing something ? kindly Clarify. Thank you for the contribution. "Bob Phillips" wrote: For the last one you need =SUMPRODUCT((DecMaxKms($B26-1))*(DecMaxKms<=($C26))* (DecTruckModel(D$23-1))*(DecTruckModel<=(D$24))* (INDIRECT("DEC"&$B$23))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Eddy Stan" wrote in message ... Hi, =SUMPRODUCT(--(DecMaxKms($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel(D $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23)) I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial ac8:ac19478, and so on..). At the same time I have a range name DecAll with range ab8:af19478, to get sum of all columns with given condition, there I get error as "Value#". Can You all help me out ? many thanks in advance. When I just put = sumproduct(decall) - it is giving result, but with condition like above it is throwing error. Am I expecting more or I have done some error, please help me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
sumproduct function / VB user defined function | Excel Discussion (Misc queries) | |||
Is SUMPRODUCT the right function to use? | Excel Worksheet Functions | |||
Help on sumproduct function | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions |