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.

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.

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.

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.

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.

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.

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.

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.

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.