quick help

[ehsas]

=SUMPRODUCT((HPACB=1)*(HPNINS<24)*(RATE={4.50055})*BAL)

In above formula I am trying to give the rate range between 4.500 to 5.500 but it giving me zero outstanding where there is outstanding.I believe there is problem In range.Could any please help.Regards,

 

[ehsas]

Book1
	A	B	C	D	E	F
1	BAL	RATE	HPACB	HPNINS	RATE	Formula
2	108000	5	1	15	4.500	0
3	29844	7	2	18	2.500
4	72612	7	1	22	4.591
5	29412	7.5	5	36	5.500
6	34200	7	3	24	4.960
7	42144	7.52	1	48	5.550
8	42876	7.5	1	20	4.916
9	102000	5	1	19	5.512
Sheet1

above is the excel file sample.

 

[ehsas]

can anyone help me how to define range in formula.Regards,

 

[Andrew Poulsom]

That's better - now you have expressed your problem clearly. The formula is:

=SUMPRODUCT((HPACB=1)*(HPNINS<24)*(RATE>=4.5)*(RATE<=5.5),BAL)

You have 2 columns headed RATE. This formula assumes RATE refers to column E.

 

[ehsas]

Thanks a lot.

=SUMPRODUCT((HPACB=1)*(HPNINS<24)*(RATE>=4.5)*(RATE<=5.5),BAL)

=SUMPRODUCT((HPACB=1)*(HPNINS>24)*(RATE>=6.5)*(RATE<=7.5),BAL)

Can i put the above two condition in one formula.If yes could help.
Regards,

 

[Andrew Poulsom]

You can only have one range named RATE, so rename cell B1 to RATE1, then you can use:

=SUMPRODUCT((HPACB=1)*(HPNINS<24)*(RATE>=4.5)*(RATE<=5.5)*(RATE1>=6.5)*(RATE1<=7.5),BAL)

 

[ehsas]

can I change above formula to if function.The if function need to be perform at each row.I want to have result at each row.Regards

 

[Andrew Poulsom]

All your criteria are hard coded eg HPACB=1. Which ones do you want to change when you copy the formula down and to what should they be referring?

 

[Iridium]

Quick help!!!!!!!???????

 

[Andrew Poulsom]

LOL Chris!

 

[ehsas]

I want to refer to cell rather than entire column.for example row 1 and row 2.