Help in Sumofs, Countifs and countifs

[Raweya]

Dear All,

Can someone help me to find formulas for the file on this link sample? I have explained everything but I'm stuck to get formulas and conditions based on scenarios.
This is related to Countifs, sumifs, IF condition as well.

I would greatly appreciate it if someone can guide me

 

[Joe4]

Please post your question here on this site, instead of providing a link to somewhere else. That site is blocked for me, and I cannot read it.

Note that you can post examples here. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Raweya]

Please post your question here on this site, instead of providing a link to somewhere else. That site is blocked for me, and I cannot read it.

Note that you can post examples here. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for the note, I haven't recognised this before.

I hope this will work.

sample of e1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ID	First Coordination Date	Second Coordination Date	Submission Date	Approval Date	Discipline	Division	Trade	Sub Trade	Building	Zone	Level	Actual % Before Buffer					Actual % After Buffer
2													Actual % First Coordination	Actual % Second Coordination	Actual % Submission	Actual % Approval	Total Before Buffer	Actual % After Buffer
3	E-B02-MEP-PL-00001	3-Feb-22	5-Feb-22	6-Feb-22	10-Feb-22	GEN-PS	PS	Arch	ELE	B02	Z01	L01	20%	20%	30%	30%	100%	125%	Scenario 1
4	E-B02-MEP-PL-00006	3-Feb-22		4-Feb-22	6-Feb-22	DD-MEP	DD	MEP	PLU	B04	FOUN	GF	40%	0%	30%	30%	50%	68%	Scenario 2
5	E-B02-Gen-PL-00009			2-Feb-22	4-Feb-22	GEN-GEN	Gen	Gen	GEN	B02	C06	GF	0%	0%	50%	50%	100%	250%	Scenario 3
6	E-B02-MEP-PL-00012			6-Feb-22		AUTH-PLU	AUTH	MEP	PLU	B07	C06	L03	0%	0%	100%	0%	100%	150%	Scenario 4
7
8	To Calculate %	First Coordination	Second Coordination	Submission	Approval	First Coordination %	Second Coordination %	Submission %	Approval %	Overall
9	Scenario 1	√	√	√	√	20%	20%	30%	30%	100%
10	Scenario 2	√		√	√	40%		30%	30%	100%
11	Scenario 3			√	√			50%	50%	100%
12	Scenario 4			√				100%		100%
13
14	Buffer Per Discipline	If this ID is under this Discipline, then multiply Col. (Q) into Cell (B15)
15	GEN-PS	25%
16	DD-MEP	35%
17	GEN-GEN	150%
18	AUTH-PLU	50%
19
Sheet2

Cell Formulas
Range	Formula
M3:M6	M3	=IF(ISBLANK($B3),,1)*F9
N3:N6	N3	=IF(ISBLANK($C3),,1)*$G9
O3:O6	O3	=IF(ISBLANK($D3),,1)*H9
P3:P6	P3	=IF(ISBLANK($E3),,1)*I9
Q3,Q5	Q3	=SUM(M3:P3)/1
R3:R6	R3	=Q3*B15+Q3
Q4	Q4	=SUM(M4:P4)/2
Q6,J9:J12	Q6	=SUM(M6:P6)

 

[Raweya]

Please post your question here on this site, instead of providing a link to somewhere else. That site is blocked for me, and I cannot read it.

Note that you can post examples here. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I have some comments in the excel file to explain what I need support in each cell.. I can't find it in the sheet.

 

[Joe4]

OK, thanks for posting the images, but I am still not clear exactly what your question is.

Can you walk us through an actual example, based on your data sample?
Which cell formula are you trying to populate?
What value should be returning in that cell?
What is the logic to arrive at that value?