Answer based on criteria available

[Istiyak]

Hi freinds,


i have large data approx 50k rows.

In last column i have a amount.

I want some specification

I.e. 1.5k-2k and so many others as i want this with the help of formula.

I have used nested if and got sucess but as u know we are able to enter only 7 criteria.


Is there any way so we can add critera and there answer in two columns.


I.e.

ColumnA

1000-2000

2000-3000

3001-3500

3501-4000

ColumnB

1.5k-2k

2k-3k

3k-3.5k

3.5k-4k


and so on.


Hope u can understand.


Regards

Istiyak

 

[Hui]

Istiyak


You may want to look at using a Sumproduct() or Countifs/Sumifs to retrieve vales in ranges


eg:

=Sumproduct((Col A>1000)*(Col A <2000)*(Col B >100)*(Col B<200)*Col C)

=Sumifs(Col C, Col A, ">"&1000, Col A, "<"&2000, Col B, ">"&100, Col B, "<"&200)

=Countifs(Col A, ">"&1000, Col A, "<"&2000, Col B, ">"&100, Col B, "<"&200)


as always, Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


As a final note Excel 2007+ allow more than 7 nested If's