JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Show count of a cell either using colour or wildcard lookup?
Hi All,
See my table test data.
I want to show a total based on the cells that are <> Not in Scope.
However I want to exclude OS Upgrade Status from the formula, so those cells do not add to the count.
Any of the cells that have a DNS listed are highlighted green if that helps, but can not be filtered.
Formula has to be in actual table qty and counted on the same row
Thanks in advance
Actual Table QTY Tablet DNS Name TW101 OS Upgrade Status Tablet DNS Name TW102 OS Upgrade Status Tablet DNS Name TW103 OS Upgrade Status Formula HERE SV7841TW101 Not in Scope SV7841TW103 Not in Scope SV0545TW102 SV0545TW103 SV7762TW101 Not in Scope SV7762TW103 SV0613TW101 SV0613TW102 Not in Scope SV5804TW101 SV5804TW102 SV5804TW103 SV7705TW101 SV7705TW102 SV7705TW103 Not in Scope SV7703TW102 SV7703TW103 SV0667TW101 SV0667TW102 SV0667TW103 Not in Scope SV0585TW102 SV0585TW103 SV0654TW101 SV0654TW102 SV0654TW103 SV0523TW101 SV0523TW102 SV0523TW103 Not in Scope SV0506TW102 Not in Scope SV0630TW101 SV0630TW102 SV0630TW103
Hi
@Peter_SSs can we expand a little on the formula to include another status in a different cell?
Looking at the below tablet Actual Tablet QTY would equal
="ALL Table Names" - "Not Found Tablet"
= Actual Tablet QTY
does that make sense?
tore ID Start Date End Date Days %week Store Name Region Name Scheduled Tablet QTY Actual Tablet QTY Tablet DNS Name TW101 OS Upgrade Status Tablet DNS Name TW102 OS Upgrade Status Tablet DNS Name TW103 OS Upgrade Status Tablet DNS Name TW104 OS Upgrade Status Tablet DNS Name TW105 OS Upgrade Status Tablet DNS Name TW106 OS Upgrade Status Tablet DNS Name TW107 OS Upgrade Status Tablet DNS Name TW108 OS Upgrade Status Tablet DNS Name TW109 OS Upgrade Status Tablet DNS Name TW110 OS Upgrade Status Tablet DNS Name TW111 OS Upgrade Status Tablet DNS Name TW112 OS Upgrade Status Tablet DNS Name TW113 OS Upgrade Status Tablet DNS Name TW114 OS Upgrade Status Tablet DNS Name TW115 OS Upgrade Status Tablet DNS Name TW116 OS Upgrade Status Tablet DNS Name TW117 OS Upgrade Status Tablet DNS Name TW118 OS Upgrade Status Tablet DNS Name TW119 OS Upgrade Status Tablet DNS Name TW120 OS Upgrade Status Tablet DNS Name TW121 OS Upgrade Status Tablet DNS Name TW122 OS Upgrade Status Tablet DNS Name TW123 OS Upgrade Status Tablet DNS Name TW124 OS Upgrade Status Tablet DNS Name TW125 OS Upgrade Status Tablet DNS Name TW126 OS Upgrade Status Tablet DNS Name TW127 OS Upgrade Status Tablet DNS Name TW128 OS Upgrade Status Tablet DNS Name TW129 OS Upgrade Status Tablet DNS Name TW130 OS Upgrade Status IM's Raised 7841 18-Jul-2022 18-Jul-2022 1 0.14 Taylor's Hill V105 8 Answer would be 7. SV7841TW101 Not Found Not in Scope Completed SV7841TW103 SV7841TW104 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope SV7841TW110 SV7841TW111 Not in Scope Not in Scope SV7841TW114 SV7841TW115 SV7841TW116 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0545 18-Jul-2022 18-Jul-2022 1 0.14 Warringal Mall V108 6 Not in Scope Not Found SV0545TW102 Completed SV0545TW103 SV0545TW104 SV0545TW105 SV0545TW106 SV0545TW107 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 7762 18-Jul-2022 18-Jul-2022 1 0.14 Mernda V109 4 SV7762TW101 Completed Not in Scope Not Found SV7762TW103 SV7762TW104 SV7762TW105 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0613 19-Jul-2022 19-Jul-2022 1 0.14 Derrimut Village V106 4 SV0613TW101 Completed SV0613TW102 Not Found Not in Scope SV0613TW104 SV0613TW105 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 5804 19-Jul-2022 19-Jul-2022 1 0.14 Tarneit Davis Road V107 9 SV5804TW101 SV5804TW102 SV5804TW103 SV5804TW104 SV5804TW105 Not in Scope SV5804TW107 SV5804TW108 SV5804TW109 SV5804TW110 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 7705 19-Jul-2022 19-Jul-2022 1 0.14 Mill Park Lakes V109 4 SV7705TW101 SV7705TW102 SV7705TW103 SV7705TW104 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 7703 20-Jul-2022 20-Jul-2022 1 0.14 Roxburgh Park V103 7 Not in Scope SV7703TW102 SV7703TW103 Not in Scope SV7703TW105 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0667 20-Jul-2022 20-Jul-2022 1 0.14 Moonee Ponds V106 3 SV0667TW101 SV0667TW102 SV0667TW103 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0585 20-Jul-2022 20-Jul-2022 1 0.14 University Hill V109 6 Not in Scope SV0585TW102 SV0585TW103 SV0585TW104 SV0585TW105 SV0585TW106 SV0585TW107 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0654 20-Jul-2022 20-Jul-2022 1 0.14 Epping V109 7 SV0654TW101 SV0654TW102 SV0654TW103 SV0654TW104 SV0654TW105 SV0654TW106 SV0654TW107 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0523 21-Jul-2022 21-Jul-2022 1 0.14 Avondale Heights V106 9 SV0523TW101 SV0523TW102 SV0523TW103 SV0523TW104 SV0523TW105 SV0523TW106 SV0523TW107 SV0523TW108 SV0523TW109 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0506 21-Jul-2022 21-Jul-2022 1 0.14 Diamond Creek V109 1 Not in Scope SV0506TW102 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope 0630 21-Jul-2022 21-Jul-2022 1 0.14 Croydon V208 5 SV0630TW101 SV0630TW102 SV0630TW103 SV0630TW104 SV0630TW105 Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope Not in Scope
How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
That is not clear to me.
So this table is an actual extract
Manually I7 & I8 have been updated to 8 & 3 Because on this row 2 of the "Tablets" were "Not Found" as show in K7 & Q*
So im trying to see if we can update that formula to deduct from the count any "Not Found" from the titled "OS Upgrade Status" Columns
Does this help clarify?
I think the mini sheet worked?
Cannot read the image and there is nothing in your mini-sheet.
Could we have a
small dummy example (with results and explanation) that only has about 8 relevant columns and 4 rows?
I think the mini sheet worked?
You have to
select the range that you want to display before clicking Mini-Sheet
12728 FY23 COL WIN10 Tablet 1909 OS Upgrade Master Schedule 20220708 V1.1.xlsx A B C D E F G H I J K L M N O P Q 1 Store ID Start Date End Date Days %week Store Name Region Name Scheduled Tablet QTY Actual Tablet QTY Tablet DNS Name TW101 OS Upgrade Status Tablet DNS Name TW102 OS Upgrade Status Tablet DNS Name TW103 OS Upgrade Status Tablet DNS Name TW104 OS Upgrade Status 3 7841 18-Jul-2022 1 0.14 Taylor's Hill V105 8 8 SV7841TW101 Completed Not in Scope SV7841TW103 Completed SV7841TW104 Completed 4 545 18-Jul-2022 1 0.14 Warringal Mall V108 6 6 Not in Scope SV0545TW102 Completed SV0545TW103 Completed SV0545TW104 Completed 5 7762 18-Jul-2022 1 0.14 Mernda V109 4 4 SV7762TW101 Completed Not in Scope SV7762TW103 Completed SV7762TW104 Completed 6 613 19-Jul-2022 1 0.14 Derrimut Village V106 4 4 SV0613TW101 Completed SV0613TW102 Completed Not in Scope SV0613TW104 Completed 7 5804 19-Jul-2022 1 0.14 Tarneit Davis Road V107 9 9 SV5804TW101 Not Found SV5804TW102 Completed SV5804TW103 Completed SV5804TW104 Completed 8 7705 19-Jul-2022 1 0.14 Mill Park Lakes V109 4 4 SV7705TW101 Completed SV7705TW102 Completed SV7705TW103 Completed SV7705TW104 Not Found
ok see if the above works better.
So Column I needs to be the current value - "Not Found" status
What does that mean?
What are the expected results for the sample data shown in post #17 and why? Explain an example or two in detail.
What does that mean?
ok
So Currently Column I is the one we worked on earlier right. this calculates all the "Tablet" DNS Names excluding the "Not In Scope"
in the example of row 7 that formula is calculating the Tablet names, however you can see in the status that there was a tablet not found. Meaning the "Actual" tablets upgraded is not longer 9 is now 8, again because 1 was not found.
Sorry if not being clear enough hopefully this helps
