How to write count if but not not if

[SAMPLEHELP]

I want to count items that are in the skirts dept but not if the sub dept is denim or sweater. How would I write that?

Dept	Store Set	Qty	Sample Status	Expected XDate	Sub Dept
JACKETS/OUTERWEAR	16JUN	1	NEW	08/31/2023	DENIM
SKIRTS	16JUN	1	NEW	08/31/2023	DENIM
DENIM	16JUN	1	NEW	08/31/2023	DRESSES
SKIRTS	16JUN	1	NEW	08/31/2023	DENIM
DRESSES	13MAY	1	NEW	08/24/2023	SWEATER
DRESSES	13MAY	1	NEW	08/24/2023	SWEATER
SKIRTS	14MAYBOM	1	NEW		SWEATER
SKIRTS	14MAYBOM	1	NEW	08/31/2023	SKIRTS

 

[Michael M]

=SUMPRODUCT(--(A2:A9="SKIRTS")*(F2:F9<>"DENIM")*(F2:F9<>"SWEATER"),C2:C9)

 

[SAMPLEHELP]

=SUMPRODUCT(--(A2:A9="SKIRTS")*(F2:F9<>"DENIM")*(F2:F9<>"SWEATER"),C2:C9)

This works! Thanks a lot! How do I edit it to say the same thing but only counting items in storesets that have "may" in it?

 

[Michael M]

This will work if "May" is actually a month,where the last 2 entries are not, they are text

=SUMPRODUCT(--(A2:A9="SKIRTS")*(F2:F9<>"DENIM")*(F2:F9<>"SWEATER")*(MONTH(B2:B9)=5)*C2:C9)

 

[SAMPLEHELP]

This will work if "May" is actually a month,where the last 2 entries are not, they are text

=SUMPRODUCT(--(A2:A9="SKIRTS")*(F2:F9<>"DENIM")*(F2:F9<>"SWEATER")*(MONTH(B2:B9)=5)*C2:C9)

oh I see. is there anyway to do it where may is text? its not meant to be a month but a name.

 

[Michael M]

Use

=SUMPRODUCT(--(A2:A9="SKIRTS")*(F2:F9<>"DENIM")*(F2:F9<>"SWEATER")*--(ISNUMBER(SEARCH("May",B2:B9)))*C2:C9)

 

[FormR]

Hi, welcome to the forum! Just another option that you could also try.

=COUNTIFS(A2:A9,"SKIRTS",F2:F9,"<>DENIM",F2:F9,"<>SWEATER",B2:B9,"*MAY*")

 

[SAMPLEHELP]

Use

=SUMPRODUCT(--(A2:A9="SKIRTS")*(F2:F9<>"DENIM")*(F2:F9<>"SWEATER")*--(ISNUMBER(SEARCH("May",B2:B9)))*C2:C9)

Thank you! it gets a little wonky when I try to add the month function but I appreciate your help.

 

[SAMPLEHELP]

Hi, welcome to the forum! Just another option that you could also try.

=COUNTIFS(A2:A9,"SKIRTS",F2:F9,"<>DENIM",F2:F9,"<>SWEATER",B2:B9,"*MAY*")

This worked! thank you very much!