Counting specific text from dated columns if another condition is met

[alm395]

I have a large amount of information that is being tracked daily. Each business unit has its own sheet that basically looks like this:

SPREADSHEET NAME: ABC

	A	B	C	D
1	Name	Division	10/1/2020	10/2/2020
2	Doe, John	CA	Active	Working From Home
3	Hill, Jane	SO	Active	Off
4	Smith, Kyle	SO	Active	Active
5	White, Jake	CR	Sick	Active
6	Black, Nancy	CR	Working From Home	Active

I have several tables already tracking all sorts of data on this information, but one manager would like their division pulled out separately. They requested the table to look like the table below with the daily counts under the dates:

SPREADSHEET NAME: HISTORICAL

	A	B	C
1	Code	10/1/2020	10/2/2020
2	Active	2	1
3	Working From Home	0	0
4	Sick	0	0
5	Off	0	1

The actual dates will range from 3/20/2020 - 12/31/2020.

What is the best formula to use saying if the Division = SO on Spreadsheet ABC, count the number of times "Active" appeared for 10/1/2020 (should = 2)?

Please help!

 

[CA_Punit]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J
3	Name	Division	10-01-2020	10-02-2020				Division Name	CA
4	Doe, John	CA	Active	Working From Home						10-01-2020
5	Hill, Jane	CA	off	Off				Active		1
6	Smith, Kyle	SO	Active	Active				Working from Home		0
7	White, Jake	CA	off	Active				Sick		0
8	Black, Nancy	CA	off	Active				off		3
9
Sheet2

Cell Formulas
Range	Formula
J5:J8	J5	=SUM(IFERROR(SEARCH(H5,IF($I$3=$B$4:$B$8,INDEX($C$4:$D$8,,MATCH($J$4,$C$3:$D$3,0))))^0,0))

Make sure that a word is not present in the word like OFF is a single word but it is also present in Playoff.
I have used Search function which will ignore this. If this is a case then please ping me again as your criteria doesn't have such case

 

[alm395]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J
3	Name	Division	10-01-2020	10-02-2020				Division Name	CA
4	Doe, John	CA	Active	Working From Home						10-01-2020
5	Hill, Jane	CA	off	Off				Active		1
6	Smith, Kyle	SO	Active	Active				Working from Home		0
7	White, Jake	CA	off	Active				Sick		0
8	Black, Nancy	CA	off	Active				off		3
9
Sheet2

Cell Formulas
Range	Formula
J5:J8	J5	=SUM(IFERROR(SEARCH(H5,IF($I$3=$B$4:$B$8,INDEX($C$4:$D$8,,MATCH($J$4,$C$3:$D$3,0))))^0,0))

Make sure that a word is not present in the word like OFF is a single word but it is also present in Playoff.
I have used Search function which will ignore this. If this is a case then please ping me again as your criteria doesn't have such case

I cannot get it to work. "0" just calculates when I sub out my information.

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D
1	Name	Division	01/10/2020	02/10/2020
2	Doe, John	CA	Active	Working From Home
3	Hill, Jane	SO	Active	Off
4	Smith, Kyle	SO	Active	Active
5	White, Jake	CR	Sick	Active
6	Black, Nancy	CR	Working From Home	Active
Sheet1

+Fluff v2.xlsm
	A	B	C
1	Code	01/10/2020	02/10/2020
2	Active	2	1
3	Working From Home	0	0
4	Sick	0	0
5	Off	0	1
Sheet2

Cell Formulas
Range	Formula
B2:C5	B2	=SUMPRODUCT((Sheet1!$B$2:$B$6="SO")*(Sheet1!$C$2:$D$6=$A2)*(Sheet1!$C$1:$D$1=B$1))

 

[alm395]

That worked!!! Thank you so much!!! You have no idea how much time you just saved me!!! ? ?

 

[Fluff]

You're welcome & thanks for the feedback.