Show count of a cell either using colour or wildcard lookup?

[excelnoob_67]

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

 

[Peter_SSs]

Is this what you mean?

22 07 14.xlsm
	A	B	C	D	E	F	G
1	Actual Table QTY	Tablet DNS Name TW101	OS Upgrade Status	Tablet DNS Name TW102	OS Upgrade Status	Tablet DNS Name TW103	OS Upgrade Status
2	2	SV7841TW101		Not in Scope		SV7841TW103
3	2	Not in Scope		SV0545TW102		SV0545TW103
4	2	SV7762TW101		Not in Scope		SV7762TW103
5	2	SV0613TW101		SV0613TW102		Not in Scope
6	3	SV5804TW101		SV5804TW102		SV5804TW103
7	3	SV7705TW101		SV7705TW102		SV7705TW103
8	2	Not in Scope		SV7703TW102		SV7703TW103
9	3	SV0667TW101		SV0667TW102		SV0667TW103
10	2	Not in Scope		SV0585TW102		SV0585TW103
11	3	SV0654TW101		SV0654TW102		SV0654TW103
12	3	SV0523TW101		SV0523TW102		SV0523TW103
13	1	Not in Scope		SV0506TW102		Not in Scope
14	3	SV0630TW101		SV0630TW102		SV0630TW103
Count

Cell Formulas
Range	Formula
A2:A14	A2	=COUNTIFS(B$1:F$1,"Tablet*",B2:F2,"<>Not in Scope")

 

[excelnoob_67]

Looks like its count my blank cells as well

Is this what you mean?

22 07 14.xlsm
	A	B	C	D	E	F	G
1	Actual Table QTY	Tablet DNS Name TW101	OS Upgrade Status	Tablet DNS Name TW102	OS Upgrade Status	Tablet DNS Name TW103	OS Upgrade Status
2	2	SV7841TW101		Not in Scope		SV7841TW103
3	2	Not in Scope		SV0545TW102		SV0545TW103
4	2	SV7762TW101		Not in Scope		SV7762TW103
5	2	SV0613TW101		SV0613TW102		Not in Scope
6	3	SV5804TW101		SV5804TW102		SV5804TW103
7	3	SV7705TW101		SV7705TW102		SV7705TW103
8	2	Not in Scope		SV7703TW102		SV7703TW103
9	3	SV0667TW101		SV0667TW102		SV0667TW103
10	2	Not in Scope		SV0585TW102		SV0585TW103
11	3	SV0654TW101		SV0654TW102		SV0654TW103
12	3	SV0523TW101		SV0523TW102		SV0523TW103
13	1	Not in Scope		SV0506TW102		Not in Scope
14	3	SV0630TW101		SV0630TW102		SV0630TW103
Count

Cell Formulas
Range	Formula
A2:A14	A2	=COUNTIFS(B$1:F$1,"Tablet*",B2:F2,"<>Not in Scope")

OK's

 

[Peter_SSs]

Looks like its count my blank cells as well

You didn't have any blanks cells in the 'Tablet' columns in your sample data so it was a bit hard to know that could happen.
(Besides, blank cells do fit the description of "cells that are <> Not in Scope")

22 07 14.xlsm
	A	B	C	D	E	F	G
1	Actual Table QTY	Tablet DNS Name TW101	OS Upgrade Status	Tablet DNS Name TW102	OS Upgrade Status	Tablet DNS Name TW103	OS Upgrade Status
2	2	SV7841TW101	mgmkjgh	Not in Scope		SV7841TW103
3	2	Not in Scope		SV0545TW102		SV0545TW103
4	1			Not in Scope		SV7762TW103
5	2	SV0613TW101		SV0613TW102		Not in Scope
6	0
7	1	SV7705TW101
8	2	Not in Scope		SV7703TW102		SV7703TW103
9	3	SV0667TW101		SV0667TW102		SV0667TW103
10	2	Not in Scope		SV0585TW102		SV0585TW103
11	3	SV0654TW101		SV0654TW102		SV0654TW103
12	3	SV0523TW101		SV0523TW102		SV0523TW103
13	1	Not in Scope		SV0506TW102		Not in Scope
14	3	SV0630TW101		SV0630TW102		SV0630TW103
Count

Cell Formulas
Range	Formula
A2:A14	A2	=COUNTIFS(B$1:F$1,"Tablet*",B2:F2,"<>Not in Scope",B2:F2,"<>")

 

[excelnoob_67]

You didn't have any blanks cells in the 'Tablet' columns in your sample data so it was a bit hard to know that could happen.
(Besides, blank cells do fit the description of "cells that are <> Not in Scope")

22 07 14.xlsm
	A	B	C	D	E	F	G
1	Actual Table QTY	Tablet DNS Name TW101	OS Upgrade Status	Tablet DNS Name TW102	OS Upgrade Status	Tablet DNS Name TW103	OS Upgrade Status
2	2	SV7841TW101	mgmkjgh	Not in Scope		SV7841TW103
3	2	Not in Scope		SV0545TW102		SV0545TW103
4	1			Not in Scope		SV7762TW103
5	2	SV0613TW101		SV0613TW102		Not in Scope
6	0
7	1	SV7705TW101
8	2	Not in Scope		SV7703TW102		SV7703TW103
9	3	SV0667TW101		SV0667TW102		SV0667TW103
10	2	Not in Scope		SV0585TW102		SV0585TW103
11	3	SV0654TW101		SV0654TW102		SV0654TW103
12	3	SV0523TW101		SV0523TW102		SV0523TW103
13	1	Not in Scope		SV0506TW102		Not in Scope
14	3	SV0630TW101		SV0630TW102		SV0630TW103
Count

Cell Formulas
Range	Formula
A2:A14	A2	=COUNTIFS(B$1:F$1,"Tablet*",B2:F2,"<>Not in Scope",B2:F2,"<>")

Ill send you a updated table when I get home.

There isn't any blank cells under tablet dns is the os progress cell it seems to be counting.

 

[Peter_SSs]

Ill send you a updated table when I get home.

OK, I'll look out for that.

 

[excelnoob_67]

Sorry @Peter_SSs I got held up.

Below is the data set im working with
You can see my actual tablet QTY is returning 30 opposed to 8

Formula below:

=COUNTIFS(J1:BR1,"Tablet*",J1:BR1,"<>Not in Scope")

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

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

30

SV7841TW101

Not in Scope

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

SV0545TW102

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

Not in Scope

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

SV0613TW102

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

 

[Peter_SSs]

You can see my actual tablet QTY is returning 30 opposed to 8

Formula below:

=COUNTIFS(J1:BR1,"Tablet*",J1:BR1,"<>Not in Scope")

My original formula
=COUNTIFS(B$1:F$1,"Tablet*",B2:F2,"<>Not in Scope")

Your formula
=COUNTIFS(J1:BR1,"Tablet*",J1:BR1,"<>Not in Scope")

You have not adapted my formula correctly to your layout.

You have removed the $ signs shown in red which lock that part of the formula to the header row.
You have changed the second range in the formula to point at the heading row, not the row the formula is in.

The formula for you in row 2 should be
=COUNTIFS(J$1:BR$1,"Tablet*",J2:BR2,"<>Not in Scope")

 

[excelnoob_67]

Thanks Peter that has it all sorted appreciate the help

 

[Peter_SSs]

You're welcome./ Thanks for the confirmation.