Calculate Filtered cells with satisfying toomany arguments..

  • Thread starter Thread starter Manikandan
  • Start date Start date
M

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.
 
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
 
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...
 
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
 
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.
 
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
 
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
 
If the cell appears blank that means the formula is returning a 0 and you
probably have 0 display turned off.
 
Back
Top