| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Filtered cells with satisfying toomany arguments..
Sir,
I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
Hi,
You need not use a filter for this. You can use the following formula =sumproduct((range_A=salesperson)*(range_B=date1) *(range_B<=date2),sum_range) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
I put the formula as given by you, but it shows cell result as #Name? only....
I put formula as =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=10-FEB-2009)*(L993:L1044<=16-FEB-2009),I993:I1044) is it right??? how can i put date in this formula?? Please reply... "Manikandan" wrote: Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
Hi,
Please input 10/2/2009 and 16/2/2009 in cell M2 and M3 respectively and then modify the formula as follows: =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=M2) *(L993:L1044<=M3),I993:I1044) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... I put the formula as given by you, but it shows cell result as #Name? only.... I put formula as =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=10-FEB-2009)*(L993:L1044<=16-FEB-2009),I993:I1044) is it right??? how can i put date in this formula?? Please reply... "Manikandan" wrote: Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
Hi,
I am very sorry, now also it is not working....there is no data in the fomula cell(just a blank only) after changing the formula.... Please help sir.... Manikandan. "Ashish Mathur" wrote: Hi, Please input 10/2/2009 and 16/2/2009 in cell M2 and M3 respectively and then modify the formula as follows: =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=M2) *(L993:L1044<=M3),I993:I1044) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... I put the formula as given by you, but it shows cell result as #Name? only.... I put formula as =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=10-FEB-2009)*(L993:L1044<=16-FEB-2009),I993:I1044) is it right??? how can i put date in this formula?? Please reply... "Manikandan" wrote: Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
Hi,
If your system is on the American date format, then 16/2/2009 will not be recognised and that may be the cause of the incorrect answer. Therefore, try to change the date to 2/16/2009. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... Hi, I am very sorry, now also it is not working....there is no data in the fomula cell(just a blank only) after changing the formula.... Please help sir.... Manikandan. "Ashish Mathur" wrote: Hi, Please input 10/2/2009 and 16/2/2009 in cell M2 and M3 respectively and then modify the formula as follows: =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=M2) *(L993:L1044<=M3),I993:I1044) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... I put the formula as given by you, but it shows cell result as #Name? only.... I put formula as =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=10-FEB-2009)*(L993:L1044<=16-FEB-2009),I993:I1044) is it right??? how can i put date in this formula?? Please reply... "Manikandan" wrote: Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
Hi,
I have try this by changing the date fomat already and now also, but still there is no answer in that cell(just shows blank)!! Please help... Manikandan "Ashish Mathur" wrote: Hi, If your system is on the American date format, then 16/2/2009 will not be recognised and that may be the cause of the incorrect answer. Therefore, try to change the date to 2/16/2009. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... Hi, I am very sorry, now also it is not working....there is no data in the fomula cell(just a blank only) after changing the formula.... Please help sir.... Manikandan. "Ashish Mathur" wrote: Hi, Please input 10/2/2009 and 16/2/2009 in cell M2 and M3 respectively and then modify the formula as follows: =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=M2) *(L993:L1044<=M3),I993:I1044) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... I put the formula as given by you, but it shows cell result as #Name? only.... I put formula as =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=10-FEB-2009)*(L993:L1044<=16-FEB-2009),I993:I1044) is it right??? how can i put date in this formula?? Please reply... "Manikandan" wrote: Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
Hi,
Please mail me the file at . Please explain the problem very clearly. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... Hi, I have try this by changing the date fomat already and now also, but still there is no answer in that cell(just shows blank)!! Please help... Manikandan "Ashish Mathur" wrote: Hi, If your system is on the American date format, then 16/2/2009 will not be recognised and that may be the cause of the incorrect answer. Therefore, try to change the date to 2/16/2009. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... Hi, I am very sorry, now also it is not working....there is no data in the fomula cell(just a blank only) after changing the formula.... Please help sir.... Manikandan. "Ashish Mathur" wrote: Hi, Please input 10/2/2009 and 16/2/2009 in cell M2 and M3 respectively and then modify the formula as follows: =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=M2) *(L993:L1044<=M3),I993:I1044) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... I put the formula as given by you, but it shows cell result as #Name? only.... I put formula as =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=10-FEB-2009)*(L993:L1044<=16-FEB-2009),I993:I1044) is it right??? how can i put date in this formula?? Please reply... "Manikandan" wrote: Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Filtered cells with satisfying toomany arguments..
If the cell appears blank that means the formula is returning a 0 and you
probably have 0 display turned off. -- Biff Microsoft Excel MVP "Manikandan" wrote in message ... Hi, I have try this by changing the date fomat already and now also, but still there is no answer in that cell(just shows blank)!! Please help... Manikandan "Ashish Mathur" wrote: Hi, If your system is on the American date format, then 16/2/2009 will not be recognised and that may be the cause of the incorrect answer. Therefore, try to change the date to 2/16/2009. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... Hi, I am very sorry, now also it is not working....there is no data in the fomula cell(just a blank only) after changing the formula.... Please help sir.... Manikandan. "Ashish Mathur" wrote: Hi, Please input 10/2/2009 and 16/2/2009 in cell M2 and M3 respectively and then modify the formula as follows: =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=M2) *(L993:L1044<=M3),I993:I1044) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manikandan" wrote in message ... I put the formula as given by you, but it shows cell result as #Name? only.... I put formula as =SUMPRODUCT((G993:G1044="RAKESH")*(L993:L1044=10-FEB-2009)*(L993:L1044<=16-FEB-2009),I993:I1044) is it right??? how can i put date in this formula?? Please reply... "Manikandan" wrote: Sir, I am having a worksheet which contains the following data. What i required is when i select a sales Person name by using filter, it should show the total value of SO VALUE where Order Receiving date is from a range(like 01.02.09 to 10.02.09). How will i get this data??? P.O No. Dealer Name Sales Person SO VALUE Order Receiving Dt. Please Help!!! Regards, M.Manikandan. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate Filtered Cells Only | Excel Worksheet Functions | |||
| non-contiguous cells as arguments to XIRR | Excel Discussion (Misc queries) | |||
| Satisfying 2 conditions and lookup from a 3rd column to return a v | Excel Worksheet Functions | |||
| Functin to calculate arguments for a product? (12= 12*1, 4*3, 6*2. | Excel Worksheet Functions | |||
| using sumif formula: but requiring satisfying 2 if statements | Excel Worksheet Functions | |||
![Manikandan[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
