Ranges in What IF FUNCTIONS- Is it possible?

Need to do an aging analysis of medical stock, in four categories according to days to expiry for a ( up to 60 days, 61 to 90 days, 91 to 365 days and over 365 days). Column E calculates the number of days. I want in columns F, G, H AND I to reflect the value of the stock according to the dayst to expiry. this information is in colunm c. How do i get a range on a what if function? IF(BG10365 days 1. 0.35 27,393 9,619.33 79. 2. 0.18 33,871 6,096.78 140. 3. 0.16 152,064 24,571.4 1113.0.00

F2: =SUMIF(--($E$2:$E$200,"<=60",$C$2:$C$200)

 

G2: =SUMIF(--($E$2:$E$200,"<=90",$C$2:$C$200)-SUMIF(--($E$2:$E$200,"<=60",$C$2:$C$200)

 

H2: =SUMIF(--($E$2:$E$200,"<=365",$C$2:$C$200)-SUMIF(--($E$2:$E$200,"<=90",$C$2:$C$200)

 

I2: =SUMIF(--($E$2:$E$200,">365",$C$2:$C$200)

Hello Again: Thank you for your response, however, I don't think my original post conveyed the essence of what i want to do, partly due to formatting of the question. As advised by the Moderator, I have attached a word document that illustrates my situation and my question further. Looking forward to your input. Regards, Steve N. Mulrain. THE SITUATION Need to do an aging analysis of medical stock, in four categories according to days to expiry for a ( up to 60 days, 61 to 90 days, 91 to 365 days and over 365 days). Days to expiration A B C D E F 1 Stock value Days to Expiry 365 days 2 6,097 49.00 =IF(B2

Steve

That's much better (bit rushed, so not checked the overlaps <=90, <91, etc), but this should get you started

C2

=IF(B2<60,A2,0)

D2

=IF(AND(B2>=60,B2<91),A2,0)

E2

=IF(AND(B2>=91,B2<366),A2,0)

F2

=IF(B2>365,A2,0)

 

Steve,

 

my solution delivered the totals in one step, no intermediate calculations.

Nick: Thank you very much, it worked well, though i had to use a semi colon rather than a comma! Go figure! =IF(BG10= 60; BG10= 91; BG10365;BE10;0) This is an invaluable tool to monitor my stock consumption patterns and to alert me to stock that i need to have regional clinics move quickly rather than write off.

Bob: Much thanks again, your solution is quite useful when amalgamating data for presentation for my Board of Directors and Exeuctive Managers. It was useful though, due to my basic excel knowledge, to be exposed to the intermediate calculations as well as to your solution that delivered the totals. Regards, Steve N. Mulrain

No sweat, I just wanted to make sure you understood what mine did.