How to use add a criteria to filter my data

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
So, in the attached screenshots, I have 2 tables. I am using 2 images here, one each showing the highlight on a "Late" or "On Time" cell so you can see the same formula except I change a "0" to a "!". Now this formula gives me the entire total from the worksheet being queried, not down to the specific warehouse in column B. Every time I try to include that as another criteria in either formula, and get past any syntax errors, either formula only returns "0" when I know there should be a resulting count. I also realize that if I can get a working formula, I wont have to use the "103,OFFSET" part since I don't want to navigate to a bunch of tabs to manually filter each one and have this formula ignoring hidden cells. If it works right, nothing needs be hidden at all manually...At least that's what I'm tying to do

Capture.JPG
Capture1.JPG


The table on the left is created by a VB script and basically all imported shipping data for a given week (With a worksheet created and named for that week.). On a summary data tab, I have the second table where I want to count the number of rows where the "On Time" column (Column F) shows a 1 for a given warehouse in column B. I want to plug this into my VB script to merely populate the summary data table with the same formula every time a new worksheet gets created for that week for each of the warehouses showing. Instead of charting from VB, I only want to drop a formula in each cell on the summary page, to pull the data from the other page.

To get this working, I have tried "SUMPRRODUCT" formulas mostly, COUNTIFS as well, but I just can't seem to get the formula to include the reference back to the specific warehouse.


=SUMPRODUCT((F2:F200=0)*(SUBTOTAL(103,OFFSET(F2:F200,ROW(F2:F200)-MIN(ROW(F2:F200)),0,1))))

=SUMPRODUCT((F2:F200=1)*(SUBTOTAL(103,OFFSET(F2:F200,ROW(F2:F200)-MIN(ROW(F2:F200)),0,1))))


Is there a way to expand this formula to count only if the warehouse (In column B) is A, B, or C?

If not, is there a different function that will include that and get me the same resulting data for my summary table broken down that specific?

I'm using this table solely to build a running year chart that shows the weekly (Which means pulling data from...all...the weekly tabs as they occur.) "on time/late" bars over the YTD for each specific warehouse.

Can someone help me sort this out?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,629
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Excel Formula:
=SUMPRODUCT(--($F$2:$F$200=0)*SUBTOTAL(103,OFFSET($F$2:$F$200,ROW($F$2:$F$200)-ROW($F$2),0,1)))
 

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
Try this:
Excel Formula:
=SUMPRODUCT(--($F$2:$F$200=0)*SUBTOTAL(103,OFFSET($F$2:$F$200,ROW($F$2:$F$200)-ROW($F$2),0,1)))

I tried that. That again returns totals of the whole data table, not sorted out by way of column "B". As an example, this is what I am trying to add:

=SUMPRODUCT(--($B$2:$B$200="CH")*(--($F$2:$F$200=1)*SUBTOTAL(103,OFFSET($F$2:$F$200,ROW($F$2:$F$200)-ROW($F$2),0,1))))

This does not cause any errors but only returns ")" no matter if I change it to "1" or "0" for the "F" column 0 or 1. I am trying to sort the data relative to column "B" which is warehouse specific. Maybe this is the wrong formula to try to do in this context, or, I have the syntax wrong?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,629
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What About:
Excel Formula:
=SUMPRODUCT((--($B$2:$B$200="CH"))*(--($F$2:$F$200=0))*SUBTOTAL(103,OFFSET($F$2:$F$200,ROW($F$2:$F$200)-ROW($F$2),0,1)))
 

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Here is a little simpler approach.

Here I am showing a list where I manually filtered the worksheet to specifically show the warehouse specific rows. For the warehouse "CH" in column B, I want to know how many times (Rows) the shipments were "On Time" or "Late".

Capture.JPG


At the bottom right corner, I have 4 cells where I first try to use COUNTIFS formulas to sort based on column B combined with column F to tell me number of times they were "1" or "0". Then I tried the same thing with the SUMPRODUCT formulas. The next column over I show the text version of the formulas, all of which return "0" regardless of what the sorted list is showing. In this case, it should be 8 on time and 5 late. or 8 x 1 and 5 x 0...In total there are 62 entries with 12 late shipments, if the entire sheet is not filtered. Formulas should still return the same counts, filtered or not.

I checked the B column to see if where I am asking it to match "CH" has no leading or trailing characters throwing of the "CH" reference due to hidden character spaces, etc. I found none. None of these formulas returned any errors but will not give counts other than 0.
 

Attachments

  • Capture.JPG
    Capture.JPG
    100.7 KB · Views: 2

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,629
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.
 

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Well I cut this down to the single worksheet in question. I hope I provided everything needed to sort the formula(s) out.


Example_File_1.xlsm
ABCDEFGHIJKLMNOP
1ord nowarehouseitem nopromise dtshipped dtOn Time
2AT 1/29/20211/19/20211
3AT 2/26/20211/19/20211
4CA 3/1/20211/19/20211
5CH 1/4/20211/19/20210
6AT 1/25/20211/19/20211
7CH 1/21/20211/19/20211
8AT 1/22/20211/19/20211
9CHP1/18/20211/19/20210
10CA 2/3/20211/20/20211
11CA 2/8/20211/20/20211
12CA 4/19/20211/20/20211
13CA 4/26/20211/20/20211
14CA 2/10/20211/20/20211
15CA 2/12/20211/21/20211
16CA 2/8/20211/21/20211
17CA 1/28/20211/21/20211
18CH 1/22/20211/21/20211
19CH 1/29/20211/21/20211
20AT 1/25/20211/21/20211
21CH 1/12/20211/21/20210
22CH 1/18/20211/21/20210
23CA 2/10/20211/21/20211
24CH 1/29/20211/21/20211
25CA 1/21/20211/21/20211
26AT 1/25/20211/22/20211
27AT 1/25/20211/22/20211
28CA 2/12/20211/22/20211
29CA 2/12/20211/22/20211
30CA 1/29/20211/22/20211
31CH 1/22/20211/22/20211
32CH 1/29/20211/22/20211
33CH 1/18/20211/22/20210
34CH 1/18/20211/22/20210
35AT 1/22/20211/22/20211
36AT 1/21/20211/22/20210
37AT 1/21/20211/22/20210
38CA 4/21/20211/22/20211
39CA 5/5/20211/22/20211
40CH 2/12/20211/22/20211
41CA 2/8/20211/22/20211
42CH 3/13/20211/22/20211
430=COUNTIFS(B2:B42,"CH",F2:F42,"=0")
440=COUNTIFS(B2:B42,"CH",F2:F42,"=1")
450=SUMPRODUCT((--($B$2:$B$42="CH"))*(--($F$2:$F$42=0))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
460=SUMPRODUCT((--($B$2:$B$42="CH"))*(--($F$2:$F$42=1))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
47
48Filter for CH in column B, then filter for 0 or 1 counts in column F for these formula samples. (Warehouse 2 letter indicator can be changed to filter for other variants.)
49
W11_9_2020
Cell Formulas
RangeFormula
F43F43=COUNTIFS(B2:B42,"CH",F2:F42,"=0")
F44F44=COUNTIFS(B2:B42,"CH",F2:F42,"=1")
F45F45=SUMPRODUCT((--($B$2:$B$42="CH"))*(--($F$2:$F$42=0))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
F46F46=SUMPRODUCT((--($B$2:$B$42="CH"))*(--($F$2:$F$42=1))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F42Cell Value=1textYES
F2:F42Cell Value=0textNO
 
Last edited:

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,629
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
try this.
You Have One More Space at the End of Words Column B.
First Trim them (Same as column I), Then Paste as Values at Column B
Then You can See Formulas At The End Of Column I working
Paste them at the end of column B
Book1
ABCDEFGHIJKL
1ord nowarehouseitem nopromise dtshipped dtOn Time
2AT 1/29/20211/19/20211AT
3AT 2/26/20211/19/20211AT
4CA 3/1/20211/19/20211CA
5CH 1/4/20211/19/20210CH
6AT 1/25/20211/19/20211AT
7CH 1/21/20211/19/20211CH
8AT 1/22/20211/19/20211AT
9CHP1/18/20211/19/20210CHP
10CA 2/3/20211/20/20211CA
11CA 2/8/20211/20/20211CA
12CA 4/19/20211/20/20211CA
13CA 4/26/20211/20/20211CA
14CA 2/10/20211/20/20211CA
15CA 2/12/20211/21/20211CA
16CA 2/8/20211/21/20211CA
17CA 1/28/20211/21/20211CA
18CH 1/22/20211/21/20211CH
19CH 1/29/20211/21/20211CH
20AT 1/25/20211/21/20211AT
21CH 1/12/20211/21/20210CH
22CH 1/18/20211/21/20210CH
23CA 2/10/20211/21/20211CA
24CH 1/29/20211/21/20211CH
25CA 1/21/20211/21/20211CA
26AT 1/25/20211/22/20211AT
27AT 1/25/20211/22/20211AT
28CA 2/12/20211/22/20211CA
29CA 2/12/20211/22/20211CA
30CA 1/29/20211/22/20211CA
31CH 1/22/20211/22/20211CH
32CH 1/29/20211/22/20211CH
33CH 1/18/20211/22/20210CH
34CH 1/18/20211/22/20210CH
35AT 1/22/20211/22/20211AT
36AT 1/21/20211/22/20210AT
37AT 1/21/20211/22/20210AT
38CA 4/21/20211/22/20211CA
39CA 5/5/20211/22/20211CA
40CH 2/12/20211/22/20211CH
41CA 2/8/20211/22/20211CA
42CH 3/13/20211/22/20211CH
435005
448008
455005
468008
47
Sheet1
Cell Formulas
RangeFormula
F43F43=COUNTIFS($B$2:$B$42,"CH ",$F$2:$F$42,0)
G43G43=COUNTIFS(B2:B42,"CH",F2:F42,"=0")
F44F44=COUNTIFS($B$2:$B$42,"CH ",$F$2:$F$42,1)
G44G44=COUNTIFS(B2:B42,"CH",F2:F42,"=1")
F45F45=SUMPRODUCT((--($B$2:$B$42="CH "))*(--($F$2:$F$42=0))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
G45,I45G45=SUMPRODUCT((--($B$2:$B$42="CH"))*(--($F$2:$F$42=0))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
F46F46=SUMPRODUCT((--($B$2:$B$42="CH "))*(--($F$2:$F$42=1))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
G46,I46G46=SUMPRODUCT((--($B$2:$B$42="CH"))*(--($F$2:$F$42=1))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
I2:I42I2=TRIM(B2)
I43I43=COUNTIFS($B$2:$B$42,"CH",$F$2:$F$42,0)
I44I44=COUNTIFS($B$2:$B$42,"CH",$F$2:$F$42,1)
K43K43=COUNTIFS($I$2:$I$42,"CH",$F$2:$F$42,0)
K44K44=COUNTIFS($I$2:$I$42,"CH",$F$2:$F$42,1)
K45K45=SUMPRODUCT((--($I$2:$I$42="CH"))*(--($F$2:$F$42=0))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
K46K46=SUMPRODUCT((--($I$2:$I$42="CH"))*(--($F$2:$F$42=1))*SUBTOTAL(103,OFFSET($F$2:$F$42,ROW($F$2:$F$42)-ROW($F$2:$F$42),0,1)))
 

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
I feel like a dunce that it came back to trimming the cell. I actually manually click it clicked in front off the first character and pressed backspace and again on the trailing end and it didn't make a difference. I get bills of material from a customer that constantly have ot be trimmed.

At any rate, that worked perfect, Sir. Thank you so much for weathering my wordy explanations and helping me get this project fixed. I appreciate it greatly.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,629
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for Feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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
Top