Countif Formula

[jayjaysb]

Hi all,

Looking for formula please

In Column C I have 2 options In Process and Awaiting Process
In Column D I have 4 options Repaired, Cannot be repaired, Awaiting Part, Log No.

Below I have 2 In Process = Repaired, 1 In Process = Cannot be repaired.
2 Awaiting Process = Awaiting Log No

Status Outcome
In Process Repaired
In Process Repaired
In Process Cannot be repaired
Awaiting Process Awaiting Log No
Awaiting Process Awaiting Log No

Thanks

JJSB

 

[Sam_D_Ben]

Excel 2013/2016
	C	D
1	In Process	Repaired
2	Awaiting Process	Cannot be repaired
3		Awaiting Part
4		Awaiting Log No
Sheet3

This seems your table. But not clear with what do you mean by "Status Outcome" and "Coutif" formula.

 

[Toadstool]

Hi JJSB,

I'm not sure what you need but as the subject is "Countif" I'm guessing you want a count of all combinations in columns C & D. Assuming neither can be blank then there's 8 options.

	C	D	E	F	G	H
WIP/Waiting	Status
In Process	Repaired	In Process	Repaired
Awaiting Process	Cannot be repaired	In Process	Cannot be repaired
In Process	Awaiting Part	In Process	Awaiting Part
Awaiting Process	Log No.	In Process	Log No.
In Process	Repaired	Awaiting Process	Repaired
Awaiting Process	Awaiting Part	Awaiting Process	Cannot be repaired
In Process	Awaiting Part	Awaiting Process	Awaiting Part
Awaiting Process	Log No.	Awaiting Process	Log No.
In Process	Repaired
Awaiting Process	Awaiting Part
In Process	Awaiting Part
Awaiting Process	Awaiting Part

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Summary[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Count[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Countif​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=COUNTIFS(C:C,F2,D:D,G2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jayjaysb]

Hi Sam, Sorry. I couldn't get it to paste the table, But Toadstool below has got what I meant. Thanks for your speedy reply JJSB

 

[jayjaysb]

Hi JJSB,

I'm not sure what you need but as the subject is "Countif" I'm guessing you want a count of all combinations in columns C & D. Assuming neither can be blank then there's 8 options.

	C	D	E	F	G	H
WIP/Waiting	Status
In Process	Repaired	In Process	Repaired
Awaiting Process	Cannot be repaired	In Process	Cannot be repaired
In Process	Awaiting Part	In Process	Awaiting Part
Awaiting Process	Log No.	In Process	Log No.
In Process	Repaired	Awaiting Process	Repaired
Awaiting Process	Awaiting Part	Awaiting Process	Cannot be repaired
In Process	Awaiting Part	Awaiting Process	Awaiting Part
Awaiting Process	Log No.	Awaiting Process	Log No.
In Process	Repaired
Awaiting Process	Awaiting Part
In Process	Awaiting Part
Awaiting Process	Awaiting Part

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Summary
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Count
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1
[/TD]

[TD="align: center"]8
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]9
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Countif​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]H2

[/TH]
[TD="align: left"]=COUNTIFS(C:C,F2,D:D,G2)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks Toadstool, Yes that is what I am looking for. Many thanks for you help. JJSB

 

[Sam_D_Ben]

Hi Sam, Sorry. I couldn't get it to paste the table, But Toadstool below has got what I meant. Thanks for your speedy reply JJSB

Thats ok, The reason i asked you about countif and stuff, cause looking at your data, seems you can easily achieve the result using "Pivot". I guess you don't need to create any extra table or any formula required.

I worked on Toadstool data to show you that.