Calculate Filtered cells with satisfying toomany arguments..

[Manikandan]

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.

 

[Ashish Mathur]

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]

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...

 

[Ashish Mathur]

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]

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]

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]

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]

Hi,

Please mail me the file at (e-mail address removed). Please explain the problem
very clearly.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

If the cell appears blank that means the formula is returning a 0 and you
probably have 0 display turned off.