Countif Formula

J

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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes

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
Toadstool said:
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
Click to expand...
 
Upvote 0
jayjaysb said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

B
  • Solved
I can manually type a date into this formula and get my desired result but if I reference a data validation list the formula does not work.
Replies
3
Views
188
Joe4
Joe4
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
983
MoonDancer
M
U
  • Solved
Transfer data but not rows with a certain blank cell
Replies
2
Views
441
UTB
U
P
  • Question Question
Process and Indentation
Replies
1
Views
214
Fuji
F
L
  • Solved
Referencing tables in formula
2
Replies
13
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

Share this page

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