formula to pick the values

[Jagadeeshbs]

Hello all

I need a formula to pick the values form the column based on the condition.


100010 1

100015 1

100016 1

100017 1

100018 1

100020 1

100021 1

100022 1

100025 1

100026 1

100036 1

100081 1

115049 1

115062 1

118007 1

122020 1

122021 1

132002 1

132004 1

214030 1

215094 1

215095 1


As per my requirement I need to Pick the values form 100010 to 100081 to another cell which is 12 as per the example.

Like this I have huge date to retrieve the values, Could someone please suggest me the right formula to retrieve the values.

 

[vijaySharma]

Jagadeesh,


When you say Pick the value do you mean any one of the 12 (as per the example above)...


Or


Do you want those 12 values in a drop down


~Vijay

 

[Jagadeeshbs]

Dear Vijay,


thanks for looking into this...


Number Value

100010 $50.00

100015 $10.00

100016 $60.00

100017 $70.00

100018 $80.00

100020 $100.00

100021 $10.00

100022 $60.00

100025 $70.00

100026 $20.00

100036 $30.00

100081 $40.00

115049 $50.00

115062 $90.00

118007 $80.00

122000 $70.00

122021 $80.00

132002 $10.00

132004 $20.00

214030 $30.00

215001 $40.00

215095 $45.00


Total value for the numbers from 100010 to 100081 is $ 600.00.

It’s like as simple as pulling the values based on the a/c # which is between two numbers.

 

[Luke M]

Assuming 100010 is in C2, and 100081 is in C3, the formula is:

=SUMIF(A:A,">="&C2,B:B)-SUMIF(A:A,">"&C3,B:B)

 

[Hui]

Luke may mean

=SUMIF(A:A,">="&C2,B:B)-SUMIF(A:A,"<"&C3,B:B)

 

[Hui]

or you could use a sumproduct

=SUMPRODUCT((A:A>=C2)*(A:A<=C3)*(B:B))

 

[vijaySharma]

Jagdeesh,


Just to simplify Hui's example...


=SUMPRODUCT((C:C>=100010)*(C:C<=100081)*(D:D))


Column C contains all the numbers and Colum D contains the Dollar values.


However as per your original query, of summing automatically are you going to use a helper sheet like below


ColumnA ColumnB ColumnC

100010 100081 -get the output here


You would need to provide the inputs on where to start and stop and this can be easily done using the above.


~Vijay

 

[Luke M]

Just a note, you can't callout entire columns in SUMPRODUCT if using XL 2003 or older.

 

[Hui]

or in 2007/10 you could use

=SUMIFS(B:B,A:A,">="&C2,A:A,"<="&C3)