CountIf Functions

J

jedilefty

Board Regular
Joined
Nov 14, 2017
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hello,

I need a formula that counts how many orders for a specific priority level are open. Here is an example of what my spreadsheet looks like:

So the results should be:
P1 = 1 order open
P2 = 2 orders open
P3 = 0 orders open
P4 = 0 orders open

Priorit LvlOrder#
P11569
P1Complete
P2Not Released
P21895
P2Complete
P3Not Released
P21459
P4Not Released
P4Complete
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
not following the expected results
what does OPEN mean in terms of the order status

i'm assuming anything that does not have "complete" or 'Not released" in

=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)

Book1
ABCDE
1P11569P11
2P1CompleteP22
3P2Not ReleasedP30
4P21895P40
5P2Complete
6P3Not Released
7P21459
8P4Not Released
9P4Complete
Sheet1
Cell Formulas
RangeFormula
D1:D4D1=UNIQUE(A1:A9)
E1:E4E1=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)
Dynamic array formulas.


also possible with sumproduct , if you have a list of items to exclude
=SUMPRODUCT(--($A$1:$A$9=D1),--(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
=SUMPRODUCT(($A$1:$A$9=D1)*(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))

countif excluding items OR.xlsx
ABCDEFGHIJKL
1P11569P11exclude list11
2P1CompleteP2222
3P2Not ReleasedP30complete00
4P21895P40not released00
5P2Complete
6P3Not Released=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)=SUMPRODUCT(($A$1:$A$9=D1)*(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))=SUMPRODUCT(--($A$1:$A$9=D1),--(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
Sheet1
Cell Formulas
RangeFormula
D1:D4D1=UNIQUE(A1:A9)
K1:K4K1=SUMPRODUCT(($A$1:$A$9=D1)*(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
L1:L4L1=SUMPRODUCT(--($A$1:$A$9=D1),--(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
E1:E4E1=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)
Dynamic array formulas.
 
Last edited:
Upvote 0
etaf said:
not following the expected results
what does OPEN mean in terms of the order status

i'm assuming anything that does not have "complete" or 'Not released" in

=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)

Book1
ABCDE
1P11569P11
2P1CompleteP22
3P2Not ReleasedP30
4P21895P40
5P2Complete
6P3Not Released
7P21459
8P4Not Released
9P4Complete
Sheet1
Cell Formulas
RangeFormula
D1:D4D1=UNIQUE(A1:A9)
E1:E4E1=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)
Dynamic array formulas.


also possible with sumproduct , if you have a list of items to exclude
=SUMPRODUCT(--($A$1:$A$9=D1),--(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
=SUMPRODUCT(($A$1:$A$9=D1)*(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))

countif excluding items OR.xlsx
ABCDEFGHIJKL
1P11569P11exclude list11
2P1CompleteP2222
3P2Not ReleasedP30complete00
4P21895P40not released00
5P2Complete
6P3Not Released=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)=SUMPRODUCT(($A$1:$A$9=D1)*(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))=SUMPRODUCT(--($A$1:$A$9=D1),--(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
Sheet1
Cell Formulas
RangeFormula
D1:D4D1=UNIQUE(A1:A9)
K1:K4K1=SUMPRODUCT(($A$1:$A$9=D1)*(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
L1:L4L1=SUMPRODUCT(--($A$1:$A$9=D1),--(ISNA(MATCH($B$1:$B$9,$I$3:$I$4,0))))
E1:E4E1=COUNTIFS($B$1:$B$9,"<>"&"complete",$B$1:$B$9,"<>"&"not released",$A$1:$A$9,D1)
Dynamic array formulas.
Click to expand...


Hi ETAF,

Correct on the assumption. In this case "Open" would equal any cell with a number value and not "Complete" or "Not Released." For each priority, I need to count how many work orders are open.

I tried using the COUNTIFS formula you provided (with some tweaks to match my tables) and unfortunately it didn't work. My data is in a table so here is how I entered the formula:

=COUNTIFS(Backlog[[#All],[Work Order]]," < >"&"Complete",Backlog[[#All],[Work Order]],"< >"&"Not Released",Table3[[#All],[Priority Lvl]],L30)

Since I'm working from a company laptop I cannot install the program that will allow me to upload my spreadsheet. I have provide screenshots to show you the sections I need to pull these calculations from:

Pulling data from here:

1688062081285.png


I need this to input the count into the column "Work Orders in WIP"

1688062175299.png
 
Upvote 0
ok, sorry not great with table references from images
have you tried using just the cell references to see if that works and gives the correct result, rather than table references

are the tables in the same workbook ?

or
if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
Maybe
Excel Formula: 
=COUNTIFS(Backlog[Work Order],"<>Complete",Backlog[Work Order],"<>Not Released",Backlog[Priority],[@[Priority Lvl]])
 
Upvote 0
Solution
etaf said:
ok, sorry not great with table references from images
have you tried using just the cell references to see if that works and gives the correct result, rather than table references

are the tables in the same workbook ?

or
if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
Click to expand...
I appreciate the help. Fluff tweaked the formula and it works now!! Thank you for your time!! :)
 
Upvote 0
I appreciate the help. Fluff tweaked the formula and it works now!! Thank you for your time!!
Click to expand...
you are welcome
glad Fluff sorted the table out for you
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Inventory disposal plan
Replies
3
Views
277
Spaceballs_the_fanboy
S
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
260
RobP
R
Z
  • Question Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
367
zero269
Z
P
  • Question Question
Problems applying formula to Excel Sharepoint File
Replies
1
Views
624
Fluff
Fluff
H
  • Question Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
682
harmless92
H

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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