COUNTIFS

[j4ymf]

Hello All

I was wondering how would write this countif formula for this count if query.

the count IF counts how may £12.80 there are, but i woul dlike it to pick up the identicle seals, so the result should be 3 and not 2.

many thanks

03.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5	Installation Date	Seal ID	Fire Rating	Level	Location	Seal Type	X	Y	Penetrating Service	Comments	Drawing Ref	Cost Breakdown	Schedule of Rates	Working Through Grid - 0.5 % uplift	Identical seals	Total Cost
6	07 Oct 2022	MN-0209	Working Through Grid - 60 min	L4	Room 48	Mastic Only Penetration	50	50	Cable		652-586 B Fire Stretegy Plan - Floor 4	Mastic Only Penetration: 19.20	£12.80	£6.40	1	£38.40
7	07 Oct 2022	JP-0003	Working Through Grid - 60 min	4	51	Mastic Only Penetration	50	50	Cable		652-586 B Fire Stretegy Plan - Floor 4	Mastic Only Penetration: 19.20	£12.80	£6.40		£19.20
8
9
10			Schedule of rates
11	Number of Seals		£12.80	£18.80	£27.80	£37.40	£46.40	£53.20	£61.30	£63.20	£71.90	£77.60	£83.20	£92.00
12	Mastic Only Penetration	31	2	0	0	0	0	0	0	0	0	0	0	0
13	Intumescent Batt and Mastic	12	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
M6:M7	M6	=IF(OR(C6={"Working Through Grid - 30 min","Working Through Grid - 60 min","Working Through Grid - 90 min","Working Through Grid - 120 min"}),IF(AND(O6>=1,O6<=5),P6/((O6+1)*1.5),P6/1.5))
N6:N7	N6	=IFS(OR(C6="Working Through Grid - 60 min",C6="Working Through Grid - 30 min",C6="Working Through Grid - 120 min"),M6/2,OR(C6="60 Min",C6="30 Min",C6="Air Seal",C6="120 Min",C6="90 Min",C6="Acoustic Seal"),"")
B12:B13	B12	=COUNTIFS( $F$6:$F$4963, A12)
C12	C12	=COUNTIFS($F$6:$F$7,$A$12,$M$6:$M$7,C11)
D12:N12	D12	=COUNTIFS($F$4:$F$895,$A$12,$M$4:$M$895,D11)
C13:N13	C13	=COUNTIFS($F$4:$F$895,$A$13,$M$4:$M$895,C11)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C22:L23,C25:L26,C18:L18,C16:L16,C12:N14,G15:L15	Cell Value	=0	text	NO

 

[Automatrix]

Do a helper column in rows 5-7 containing the total number of seals (= Identical seals + 1). Then do SUMIFS on the helper column.

If you don't want an extra column, you can add a SUMIFS on the Identical column to your COUNTIFS.

=COUNTIFS($F$6:$F$7,$A$12,$M$6:$M$7,C11)+SUMIFS($O$6:$O$7,$F$6:$F$7,$A$12,$M$6:$M$7,C11)

 

[j4ymf]

Thank you Automatrix, that's fantastic.
Much appreciated

 

[Automatrix]

You're welcome. You should consider arranging your data in a named table, as it will allow you to use (much more readable) structured references in your formulas.

=COUNTIFS(Table1[Seal Type],$A$12,Table1[Schedule of Rates],C11)+SUMIFS(Table1[Identical seals],Table1[Seal Type],$A$12,Table1[Schedule of Rates],C11)

This will also allow you to add data to your table without having to change the formulas.

 

[Fluff]

You should consider arranging your data in a named table, as it will allow you to use (much more readable) structured references in your formulas.

That is very subjective. I find that structured references are far harder to read than a normal range & can make a short formula very long thereby also making it harder to read.
IMO there are far more cons to a table than pros.