Maxif - VBA or Formula

Dear Experts, Please find attached example excel file with query. Request to solve the same i. e. using formula or VBA.

Thanks in Advance.

2 suggestions.

First don't use whole column range in your formula. Makes sheet too slow specially if you use ARRAY formulas(as you do)

Also as you use Excel 2007, you can use IFERROR.

So in your first sheet you can use your Lookup like this.

=IFERROR(VLOOKUP(A447,Deduction!$A$4:$X$1000,24,"false"),0)

But the most important is your MAX(IF...ARRAY formula(column X) that you use in your second sheet usin Whole column range. This "kills" your computer. Try to use something like this there..

=SUMPRODUCT(MAX(($A$4:$A$1000=A4)*$W$4:$W$1000))

Dear Sir,
Thanks for your reply.
One more thing , is there any way that if i insert or enter in new row that sumproduct formula range increase (i. e. We are using up to 1000 row if i entered data in Roaw 1001, range of formula increase automatic.

You can use Dynamic Named Ranges for this.

See to the link how to do this. If you can not handle this just let me know.

Another way is to use a bigger range. For example if your data are until now in row 1000 and you know that you have almost 20..new entries per day, fix your range to row 5000.(for example) This will be ok for several months...

Dear Sir,
I am unable to understand dynamic name range, can u send me as example in my attache sheet

As i see again your issue, i believe that you need in column X, only a simple countif function.,,,

=COUNTIF(List;A4)

I created a dynamic range for column A, using this formula...

=OFFSET(Deduction!$A$4;0;0;COUNTA(Deduction!$A:$A);1)

..And named this as List.