Filter using row and column

**raghunaik** · 10-13-2009, 11:30 PM

Hi All,
I have set of data(attached example file) in which i have to categorize the data depending on the LOC (column C) which is below 1000,between 1000-3000 and greater than 3000.
Once i have filtered data i need to get the counts for each impacts.
I have put the sample output in next tab(sheet 2)

Need help here

**DonkeyOte** · 10-14-2009, 05:46 AM

I confess I'm not entirely sure I follow as I can't validate your "suggested" results to your sample data... I would have expected something like the below for the first category (< 1000)

B4:
=SUMPRODUCT((Sheet1!$C$3:$C$14<1000)*(Sheet1!$D$1:$U$1=B$3)*ISNUMBER(Sheet1!$D$3:$U$14))
copied to D4

B5:
=SUMPRODUCT((Sheet1!$C$3:$C$14<1000)*(Sheet1!$D$1:$U$1=B$3)*Sheet1!$D$3:$U$14)
copied to D5

If the above is not what you want then can you elaborate further as to why the results in your file are as you suggest because I for one could not fathom.

**raghunaik** · 10-14-2009, 07:33 AM

thanks for your reply

formula works fine for the data i have.. i was just thinking what if i have more columns and more rows(assuming i have 25 more rows and 25 more columns ).. how could i make the formula more generic regardless of data in the sheet..

**DonkeyOte** · 10-14-2009, 07:34 AM

...adjust the ranges

(if you need specific examples provide specific information)

**royUK** · 10-14-2009, 07:40 AM

Look at using Dynamic Named Ranges in your formulas

**raghunaik** · 10-14-2009, 07:46 AM

I understood the part of adjusting the ranges but don't have clue about
using Dynamic Named Ranges in the formula

**DonkeyOte** · 10-14-2009, 07:48 AM

for general info. on names and DNRs see: http://www.contextures.com/xlNames01.html

however, I would add that given the complexity of the formula this might not be trivial for you to setup - if in doubt post back with more specific info.