My Filter to Excel Formula

**mckoy_1** · 11-01-2006, 09:05 PM

In the file I have a filter I use in a different system. Is there a way to convert my filter into a formula in excel to get the same result? The Data will always be located in the same cells for my reports.

Thanks,

Mckoy

**VBA Noob** · 11-02-2006, 03:16 AM

Hi,

Try this

=SUMPRODUCT(--(UPPER(LEFT($C$3:$C$200,1))>="L")*(--(UPPER(LEFT($C$3:$C$200,1))<="Z")*(--($G$3:$G$200>=D16)*(--($G$3:$G$200<=D17)*(--(K3:K200))))))

D16 = start date
D17= End Date

VBA Noob

**mckoy_1** · 11-03-2006, 03:34 PM

Thanks, this works great. However, when I linked that same formula to another workbook I get an incorrect amount.

**VBA Noob** · 11-03-2006, 03:36 PM

Can you post the sheet ??

VBA Noob

**mckoy_1** · 11-03-2006, 04:16 PM

...I must be doing something wrong. I have attached two workbooks...Data and Formula.

I basically added more rows to the formula...added 5000 rows to be exact.

here we go!

**VBA Noob** · 11-03-2006, 04:19 PM

Can you post the modified formula

VBA Noob

**mckoy_1** · 11-03-2006, 04:30 PM

Sorry for some reason the formula sheet was too big, So I had to delete the formula.

**VBA Noob** · 11-03-2006, 04:40 PM

Hi,

Try this example

http://cjoint.com/?ldvNkURzxG

VBA Noob

**mckoy_1** · 11-03-2006, 04:55 PM

It works perfectly but I need to have this formula on a separate workbook. You see the Data will be exported from another source. I would like to link-update each time I open the formula workbook.

**VBA Noob** · 11-03-2006, 05:08 PM

Hi,

You just need to add the name of the workbook + tab e.g

=SUMPRODUCT(--(UPPER(LEFT([DataB.xls]Data!$C$3:$C$5000,1))>="L")*(--(UPPER(LEFT([DataB.xls]Data!$C$3:$C$5000,1))<="Z")*(--([DataB.xls]Data!$G$3:$G$5000>=C9)*(--([DataB.xls]Data!$G$3:$G$5000<=D9)*(--([DataB.xls]Data!$K$3:$K$5000))))))

C9 and D9 hold the dates

VBA Noob

**mckoy_1** · 11-03-2006, 05:24 PM

Got It! Really do appreciated.

**VBA Noob** · 11-03-2006, 05:26 PM

No Problem

VBA Noob

**mckoy_1** · 02-02-2007, 02:52 PM

i got another problem...with same structure. I would like to change the alpha. For example: i have now on the previous sheet.... A - K and would to change to A - Gk....i tried changing the formula but I dont get the correct figure. Any help.