List which rows contain certain criteria

D

davey4444

Board Regular
Joined
Nov 16, 2010
Messages
97
As the title suggests I'm trying to show which rows in my spreadsheet contain a certain criteria, in this case those which are D(eliver), have a Quantity of more than 0 and have a haulage level of 0.
My sumproduct formula shows a 1 if these criteria are fulfilled and I'm able to use the Small array formula to list which rows contain 1 however I'd like a cleaner way of doing it.
Ideally I'd like the sumproduct formula (or equivalent) to be within the Small formula but when I try this it just fails.

Any help is appreciated.

Thanks!



Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">123</td><td style=";">Collect / Deliver</td><td style=";">Quantity</td><td style=";">Haulage</td><td style=";">Error?</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Product 1</td><td style=";">D</td><td style="text-align: right;;">41</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Product 2</td><td style=";">D</td><td style="text-align: right;;">11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Product 3</td><td style=";">C</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Product 4</td><td style=";">D</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Product 5</td><td style=";">D</td><td style="text-align: right;;">33</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;color: #FF0000;;">
</td><td style="text-align: right;;">3</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;color: #FF0000;;">
</td><td style="text-align: right;;">6</td></tr></tbody></table>
Sheet1​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E2</th><td style="text-align:left">=SUMPRODUCT((B2="D")*(C2>0)*(D2=0))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E3</th><td style="text-align:left">=SUMPRODUCT((B3="D")*(C3>0)*(D3=0))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E4</th><td style="text-align:left">=SUMPRODUCT((B4="D")*(C4>0)*(D4=0))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E5</th><td style="text-align:left">=SUMPRODUCT((B5="D")*(C5>0)*(D5=0))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E6</th><td style="text-align:left">=SUMPRODUCT((B6="D")*(C6>0)*(D6=0))</td></tr></tbody></table></td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E9</th><td style="text-align:left">{=SMALL(IF($E$1:$E$7=1,ROW($A$1:$A$7)),ROW(1:1))}</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E10</th><td style="text-align:left">{=SMALL(IF($E$1:$E$7=1,ROW($A$1:$A$7)),ROW(2:2))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
davey4444 said:
As the title suggests I'm trying to show which rows in my spreadsheet contain a certain criteria, in this case those which are D(eliver), have a Quantity of more than 0 and have a haulage level of 0.
My sumproduct formula shows a 1 if these criteria are fulfilled and I'm able to use the Small array formula to list which rows contain 1 however I'd like a cleaner way of doing it.
Ideally I'd like the sumproduct formula (or equivalent) to be within the Small formula but when I try this it just fails.

Any help is appreciated.

Thanks!




Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>

</TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">123</TD><TD>Collect / Deliver</TD><TD>Quantity</TD><TD>Haulage</TD><TD>Error?</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>Product 1</TD><TD>D</TD><TD style="TEXT-ALIGN: right">41</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Product 2</TD><TD>D</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Product 3</TD><TD>C</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Product 4</TD><TD>D</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Product 5</TD><TD>D</TD><TD style="TEXT-ALIGN: right">33</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">6</TD></TR></TBODY></TABLE>
Sheet1​



<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E2</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((B2="D")*(C2>0)*(D2=0))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((B3="D")*(C3>0)*(D3=0))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E4</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((B4="D")*(C4>0)*(D4=0))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E5</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((B5="D")*(C5>0)*(D5=0))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E6</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((B6="D")*(C6>0)*(D6=0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E9</TH><TD style="TEXT-ALIGN: left">{=SMALL(IF($E$1:$E$7=1,ROW($A$1:$A$7)),ROW(1:1))}</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E10</TH><TD style="TEXT-ALIGN: left">{=SMALL(IF($E$1:$E$7=1,ROW($A$1:$A$7)),ROW(2:2))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>
Click to expand...
Try this...

Enter this formula in D9. This will return the count of records that meet the criteria.

=COUNTIFS(B2:B6,"D",C2:C6,">0",D2:D6,0)

Enter this array formula** in E9 and copy down until you get blanks. This will return the row numbers:

=IF(ROWS(E$9:E9)>D$9,"",SMALL(IF(B$2:B$6="D",IF(C$2:C$6>0,IF(D$2:D$6=0,ROW(A$2:A$6)))),ROWS(E$9:E9)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the column D data range and no TEXT entries within the column C data range.
 
Upvote 0
D8 copied down:
Excel Workbook
ABCD
1123Collect / DeliverQuantityHaulage
2Product 1D4115
3Product 2D110
4Product 3C20
5Product 4D00
6Product 5D330
7
83
96
Sheet17
 
Upvote 0
But perhaps use conditional formatting;
here I selected A2:D6, selecting started from A2 so A2 was the active cell, while I applied the conditional formatting formula shown:
Excel Workbook
ABCD
1123Collect / DeliverQuantityHaulage
2Product 1D4115
3Product 2D110
4Product 3C20
5Product 4D00
6Product 5D330
Sheet17
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =AND($B2="D",$C2>0,$D2=0)Abc
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
89
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
663
Micron
M
P
  • Solved
Compile error: method or data member not found
Replies
6
Views
501
Patriot2879
P

Forum statistics

Threads
1,224,566
Messages
6,179,555
Members
452,928
Latest member
101blockchains

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