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.

 

[awoohaw]

update your formula to this:

=SUM(
(--(H$1=$B$2:$B$20))*(--($G2=$A$2:$A$20))*
((--("CA"=$M$2:$M$20))+(--("NV"=$M$2:$M$20)))
)

I think that should work.

 

[SandsB]

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

Someone posted a fix for this twist but now I can't find it. I copied it down as
=SUM((--(H$1=$B$2:$B$20))*(--($G2=$A$2:$A$20))*(--("CA"=$M$2:$M$20))+(--(H$1=$B$2:$B$20))*(--($G2=$A$2:$A$20))*(--("NV"=$M$2:$M$20))) and it's not working for me. I'm not sure if I copied it wrong because I'm still trying to figure out how this solution works.
In my file, I used one of the other solutions for the initial question - it works and I'm smart enough to understand why it works but not smart enough to add the new twist of excluding 2 states.
This is the formula I was using: =COUNTIFS(A:A,E2,B:B,$F$1:$G$1)
Could the kind soul who posted the fix report it? Or does anyone know how to make it work with this formula? =COUNTIFS(A:A,E2,B:B,$F$1:$G$1)
Thanks to all.

 

[awoohaw]

that was probably me:
=SUM(
(--(H$1=$B$2:$B$20))*(--($G2=$A$2:$A$20))*
((--("CA"=$M$2:$M$20))+(--("NV"=$M$2:$M$20)))
)
I was trying to use the communicative property there. Maybe i have a paren wrong... try this then:
=SUM(
(--(H$1=$B$2:$B$20))*(--($G2=$A$2:$A$20))*((--("CA"=$M$2:$M$20))
)
+
SUM(
(--(H$1=$B$2:$B$20))*(--($G2=$A$2:$A$20))*(--("NV"=$M$2:$M$20)))
)

I am not sure the parens are correct.

 

[awoohaw]

to amend @Fluff 's formula maybe this will work:

=COUNTIFS(A:A,E2,B:B,$F$1:$G$1,M:M,"NV") + COUNTIFS(A:A,E2,B:B,$F$1:$G$1,M:M,"CA")

 

[Fluff]

How about

Fluff.xlsm
	A	B											M	N	O	P	Q
1	Date	Text														TextString 1	TextString 2
2	19/02/2023	TextString 1											ca		19/02/2023	1	1
3	19/02/2023	TextString 1													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											nv
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
Main

Cell Formulas
Range	Formula
O2:O4	O2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
P2:Q2	P2	=COUNTIFS(A:A,O2,B:B,$P$1:$Q$1,M:M,"<>CA",M:M,"<>NV")
P3:Q4	P3	=COUNTIFS(A:A,O3,B:B,$P$1:$Q$1)
Dynamic array formulas.

 

[SandsB]

I ended up using countif:
=COUNTIFS(Data!$R$2:$R$20000,$A3,Data!$D$2:$D$20000,$B$2)-(COUNTIFS(Data!$R$2:$R$20000,$A3,Data!$D$2:$D$20000,$B$2,Data!$I$2:$I$20000,"CA"))-(COUNTIFS(Data!$R$2:$R$20000,$A3,Data!$D$2:$D$20000,$B$2,Data!$I$2:$I$20000,"NV"))

I need to play some more with the other options provided here. Thanks to all.

 

[Fluff]

Glad we could help & thanks for the feedback.