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
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?
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?