Planning, Scheduling & Programming Discussion Member for 14 years 7 months Member for 14 years 7 months Submitted by jithin on Sun, 2021-11-21 13:55 Permalink Thanks You, Both the formula Thanks You, Both the formula Works fine now. Rafael, Its just for a plotting an example, not in actual and I have considered foundation for a Pump which is small. Regards Jithin Member for 21 years 8 months Member for 21 years 8 months Submitted by Rafael Davila on Thu, 2021-11-11 00:44 Permalink And what about nonworking And what about nonworking days and days with fewer work hours? Weird weightage of excavation is less than weightage of curing, well the whole concept of weightages I find it flawed. Member for 8 years 1 month Member for 8 years 2 months Submitted by StevenA on Wed, 2021-11-10 18:10 Permalink Jithin,Try using SUMIFUsing Jithin, Try using SUMIF Using Rodel's template, the calculation would be: =SUMIF(B3:B9,"<="&B1,C3:C9) Dont forget to include the quotation marks around the "<=" part. SUMIF(Range,Criteria,SumRange) Regards, Steven Member for 19 years Member for 19 years Submitted by Rodel Marasigan on Wed, 2021-11-10 12:07 Permalink Hi Jithin,Yes. Just change Hi Jithin, Yes. Just change the match type to 1 instead of 0. Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,1),1)) Member for 14 years 7 months Member for 14 years 7 months Submitted by jithin on Mon, 2021-11-08 12:15 Permalink Thanks Rodel,It does work but Thanks Rodel, It does work but there is a small catch here. If the date is not mentioned in Cell B1 doesnt match the dates in Cell B3:B9, then the result is #N/A. Say in the above table in B1 if I put 4-Nov-21, Result = #N/A. It should rather give 15%. Any way to sort this out. Thanks Jithin Member for 19 years Member for 19 years Submitted by Rodel Marasigan on Wed, 2021-11-03 07:32 Permalink Yes, you can use sum function Yes, you can use sum function with offset and match to get the correct range. I assumed that your lookup is the date. Column A = Activity Column B = Date Column C = Weightage Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,0),1)) Log in or register to post comments
Member for 14 years 7 months Member for 14 years 7 months Submitted by jithin on Sun, 2021-11-21 13:55 Permalink Thanks You, Both the formula Thanks You, Both the formula Works fine now. Rafael, Its just for a plotting an example, not in actual and I have considered foundation for a Pump which is small. Regards Jithin
Member for 21 years 8 months Member for 21 years 8 months Submitted by Rafael Davila on Thu, 2021-11-11 00:44 Permalink And what about nonworking And what about nonworking days and days with fewer work hours? Weird weightage of excavation is less than weightage of curing, well the whole concept of weightages I find it flawed.
Member for 8 years 1 month Member for 8 years 2 months Submitted by StevenA on Wed, 2021-11-10 18:10 Permalink Jithin,Try using SUMIFUsing Jithin, Try using SUMIF Using Rodel's template, the calculation would be: =SUMIF(B3:B9,"<="&B1,C3:C9) Dont forget to include the quotation marks around the "<=" part. SUMIF(Range,Criteria,SumRange) Regards, Steven
Member for 19 years Member for 19 years Submitted by Rodel Marasigan on Wed, 2021-11-10 12:07 Permalink Hi Jithin,Yes. Just change Hi Jithin, Yes. Just change the match type to 1 instead of 0. Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,1),1))
Member for 14 years 7 months Member for 14 years 7 months Submitted by jithin on Mon, 2021-11-08 12:15 Permalink Thanks Rodel,It does work but Thanks Rodel, It does work but there is a small catch here. If the date is not mentioned in Cell B1 doesnt match the dates in Cell B3:B9, then the result is #N/A. Say in the above table in B1 if I put 4-Nov-21, Result = #N/A. It should rather give 15%. Any way to sort this out. Thanks Jithin
Member for 19 years Member for 19 years Submitted by Rodel Marasigan on Wed, 2021-11-03 07:32 Permalink Yes, you can use sum function Yes, you can use sum function with offset and match to get the correct range. I assumed that your lookup is the date. Column A = Activity Column B = Date Column C = Weightage Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,0),1))
Member for
14 years 7 monthsThanks You, Both the formula
Thanks You,
Both the formula Works fine now.
Rafael,
Its just for a plotting an example, not in actual and I have considered foundation for a Pump which is small.
Regards
Jithin
Member for
21 years 8 monthsAnd what about nonworking
And what about nonworking days and days with fewer work hours?
Weird weightage of excavation is less than weightage of curing, well the whole concept of weightages I find it flawed.
Member for
8 years 1 monthJithin,Try using SUMIFUsing
Jithin,
Try using SUMIF
Using Rodel's template, the calculation would be:
=SUMIF(B3:B9,"<="&B1,C3:C9)
Dont forget to include the quotation marks around the "<=" part.
SUMIF(Range,Criteria,SumRange)
Regards,
Steven
Member for
19 yearsHi Jithin,Yes. Just change
Hi Jithin,
Yes. Just change the match type to 1 instead of 0.
Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,1),1))
Member for
14 years 7 monthsThanks Rodel,It does work but
Thanks Rodel,
It does work but there is a small catch here.
If the date is not mentioned in Cell B1 doesnt match the dates in Cell B3:B9, then the result is #N/A.
Say in the above table in B1 if I put 4-Nov-21, Result = #N/A.
It should rather give 15%.
Any way to sort this out.
Thanks
Jithin
Member for
19 yearsYes, you can use sum function
Yes, you can use sum function with offset and match to get the correct range. I assumed that your lookup is the date.
Column A = Activity
Column B = Date
Column C = Weightage
Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,0),1))