Getting a count

[SandsB]

Sheet1 column A contains dates, Column B contains one of 5 text strings. There may be 100 rows for 2/22/23, then 20 rows for 2/23/23, then 50 rows for 2/34/23, etc.
I need cell A1 in Sheet2 to look at Sheet1 and give me the count of all records for 2/22/23 that contain "text string 1". And B1 is the same for "text string 2."
Then A2 and B2 are the same things for 2/23/23
Then A3 and B3 are etc.

So A1 in Sheet2 should be a formula that reads "Look at all the rows in Sheet 1 that contain 2/22/23 in column A and tell me how many of those rows have "text string 1" in column B.

 

[SandsB]

I'm thinking a Countif but I can't see a way to do that based on date

 

[awoohaw]

here are two ways:
You would need to update sheet/cell references.

Mr Excel 8.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Text				TextString 1			TextString 1
2	2023-02-19	TextString 1			2023-02-19	2		2023-02-19	2
3	2023-02-19	TextString 2			2023-02-20	2		2023-02-20	2
4	2023-02-19	TextString 3			2023-02-21	0		2023-02-21	0
5	2023-02-19	TextString 4
6	2023-02-19	TextString 5
7	2023-02-19	TextString 1
8	2023-02-19	TextString 2
9	2023-02-19	TextString 3
10	2023-02-19	TextString 4
11	2023-02-19	TextString 5
12	2023-02-20	TextString 1
13	2023-02-20	TextString 2
14	2023-02-20	TextString 3
15	2023-02-20	TextString 4
16	2023-02-20	TextString 5
17	2023-02-20	TextString 1
18	2023-02-21	TextString 2
19	2023-02-21	TextString 3
20	2023-02-21	TextString 4
21
Sheet11

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIFS($A$2:$A$20,$E2,$B$2:$B$20,F$1)
I2:I4	I2	=SUM((--(I$1=$B$2:$B$20))*(--($H2=$A$2:$A$20)))

 

[awoohaw]

A little better example of the same...

Mr Excel 8.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Text			TextString 1	TextString 2		TextString 1	TextString 2
2	2023-02-19	TextString 1		2023-02-19	2	2	2023-02-19	2	2
3	2023-02-19	TextString 2		2023-02-20	2	1	2023-02-20	2	1
4	2023-02-19	TextString 3		2023-02-21	0	1	2023-02-21	0	1
5	2023-02-19	TextString 4
6	2023-02-19	TextString 5
7	2023-02-19	TextString 1
8	2023-02-19	TextString 2
9	2023-02-19	TextString 3
10	2023-02-19	TextString 4
11	2023-02-19	TextString 5
12	2023-02-20	TextString 1
13	2023-02-20	TextString 2
14	2023-02-20	TextString 3
15	2023-02-20	TextString 4
16	2023-02-20	TextString 5
17	2023-02-20	TextString 1
18	2023-02-21	TextString 2
19	2023-02-21	TextString 3
20	2023-02-21	TextString 4
21
Sheet11

Cell Formulas
Range	Formula
E2:F4	E2	=COUNTIFS($A$2:$A$20,$D2,$B$2:$B$20,E$1)
H2:I4	H2	=SUM((--(H$1=$B$2:$B$20))*(--($G2=$A$2:$A$20)))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Date	Text				TextString 1	TextString 2
2	19/02/2023	TextString 1			19/02/2023	2	2
3	19/02/2023	TextString 2			20/02/2023	2	1
4	19/02/2023	TextString 3			21/02/2023	0	1
5	19/02/2023	TextString 4
6	19/02/2023	TextString 5
7	19/02/2023	TextString 1
8	19/02/2023	TextString 2
9	19/02/2023	TextString 3
10	19/02/2023	TextString 4
11	19/02/2023	TextString 5
12	20/02/2023	TextString 1
13	20/02/2023	TextString 2
14	20/02/2023	TextString 3
15	20/02/2023	TextString 4
16	20/02/2023	TextString 5
17	20/02/2023	TextString 1
18	21/02/2023	TextString 2
19	21/02/2023	TextString 3
20	21/02/2023	TextString 4
21
Data

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
F2:G4	F2	=COUNTIFS(A:A,E2,B:B,$F$1:$G$1)
Dynamic array formulas.

 

[SandsB]

Fantastic - you taught me a great trick. Thank you.

 

[awoohaw]

Fantastic - you taught me a great trick. Thank you.

Not sure if that is for @Fluff or me, but regardless I'm pleased you found a solution.

If you believe one of the posts here has provided you with a solution, please mark it as the answer.

 

[alansidman]

One more solution for your consideration with Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Text"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book4
	A	B	C	D	E	F
1	Date	Text		Date	Text	Count
2	2/19/2023	TextString 1		2/19/2023	TextString 1	2
3	2/19/2023	TextString 2		2/19/2023	TextString 2	2
4	2/19/2023	TextString 3		2/19/2023	TextString 3	2
5	2/19/2023	TextString 4		2/19/2023	TextString 4	2
6	2/19/2023	TextString 5		2/19/2023	TextString 5	2
7	2/19/2023	TextString 1		2/20/2023	TextString 1	2
8	2/19/2023	TextString 2		2/20/2023	TextString 2	1
9	2/19/2023	TextString 3		2/20/2023	TextString 3	1
10	2/19/2023	TextString 4		2/20/2023	TextString 4	1
11	2/19/2023	TextString 5		2/20/2023	TextString 5	1
12	2/20/2023	TextString 1		2/21/2023	TextString 2	1
13	2/20/2023	TextString 2		2/21/2023	TextString 3	1
14	2/20/2023	TextString 3		2/21/2023	TextString 4	1
15	2/20/2023	TextString 4
16	2/20/2023	TextString 5
17	2/20/2023	TextString 1
18	2/21/2023	TextString 2
19	2/21/2023	TextString 3
20	2/21/2023	TextString 4
Sheet2

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[SandsB]

Glad we could help & thanks for the feedback.

A new twist - the boss wants this same count EXCEPT - if the value in column M is equal to CA or NV.