Countif Formula

jayjaysb

Board Regular
Joined
Nov 16, 2011
Messages
204
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Excel 2013/2016
CD
1In ProcessRepaired
2Awaiting ProcessCannot be repaired
3Awaiting Part
4Awaiting Log No
Sheet3


This seems your table. But not clear with what do you mean by "Status Outcome" and "Coutif" formula.
 
Upvote 0
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.



CDEFGH
WIP/WaitingStatus
In ProcessRepairedIn ProcessRepaired
Awaiting ProcessCannot be repairedIn ProcessCannot be repaired
In ProcessAwaiting PartIn ProcessAwaiting Part
Awaiting ProcessLog No.In ProcessLog No.
In ProcessRepairedAwaiting ProcessRepaired
Awaiting ProcessAwaiting PartAwaiting ProcessCannot be repaired
In ProcessAwaiting PartAwaiting ProcessAwaiting Part
Awaiting ProcessLog No.Awaiting ProcessLog No.
In ProcessRepaired
Awaiting ProcessAwaiting Part
In ProcessAwaiting Part
Awaiting ProcessAwaiting 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]
 
Upvote 0
Solution
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
 
Upvote 0
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
 
Upvote 0
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.

OTzQDVE.jpg
 
Upvote 0

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top