SUMPRODUCT/SUMIFS....ALGO NEEDED

[X-YELL]

[pre]

A                 B              C               D            E
90               95.2           76.34            23           0
95.23            100.33         102              45           0
100.34           105.4          103.4            15           =45+15
105.67.          110            106              100          =100+20+40
107              20
108              40

[/pre]

DEAR GUYS


wat i want is to get the sum of all the values coresponding to column D if each value under column C lies in the range between two columns A and B . meeans in the range 105.67 to 110 values in coulmn C are 106,107 ,108 so the result should be against this would be sum of 100+20+40 similarly in the range 100.34 to 105 .4 the result would be 45 +15


Thanks Guys


Please reply

I,ver tried with sumifs and sumproduct but the result it gives the highest no ....

 

[srinidhi]

Try the If & AND formula, where the condition matches the result in D2 will be displayed, if the number in c column does not fall in the A & B Range then 0 will be returned.


Place this formula in E =IF(AND(C2>A2,C2<B2),D2,0)& sum E column.

 

[X-YELL]

[pre]

A	B	   C

100	101.98	      2+4=6
102.3	104.4	     5
104.45	107.34	     0
107.34	110	    6+7+8

X	Y
101.45	2
100.88	4
103.4	5
108.23	6
109.44	7
109.98	8

[/pre]

THE VALUES UNDER COULMN X SHOULD BE COMPARED WITH VALUES IN COLUMN A AND BAND IF IT LIES IN RANGE IT GIVES THE SUM OF VALUES FROM COULMN Y ..THE SUM OF VALUES IS IN COULMN C

 

[X-YELL]

thnks for reply but i need to insert excel format in thread so tht problem can easily understood..but i dont know how ?? help

 

[Hui]

X-YELL


Firstly, Welcome to the Chandoo.org Forums


Can you please clarify the problem as the Your Two posts are slightly different?


Can you please also read the Two Green Sticky posts at the top of http://chandoo.org/forums/

Posting Rules & Etiquette [read before posting]

&

Posting a sample workbook

 

[X-YELL]

thanks Hui ...