=COUNTIFS Not working correctly?

[Dubyah]

Hey Guys,

Im running into an issue with my =countifs formula it is as follows. Some how the total is off...

=COUNTIFS('Nov-11-2015-'!N2:N748,"*B1*",'Nov-11-2015-'!J2:J748,"*Small*")
=COUNTIFS('Nov-11-2015-'!N2:N748,"*B1*",'Nov-11-2015-'!J2:J748,"*Medium*")
=COUNTIFS('Nov-11-2015-'!N2:N748,"*B1*",'Nov-11-2015-'!J2:J748,"*Large*")
=COUNTIFS('Nov-11-2015-'!N2:N748,"*B1*",'Nov-11-2015-'!J2:J748,"*XLarge*")

criteria1 = Batch ( I have 4 different batches ) (B1,B2,S1,S2) However S1 and S2 do not require a size.
criteria2 = Size

I want the formula to count all B1 if small, medium, large, XLarge. Which it seems to be doing yet my range is only 748 cells and my totals are over 800 which DOES NOT MAKE SENSE!

I have also attached an example file....
Unfortunately I cant include the entire range..

 

[Dubyah]

Whoops forgot to include formulas...

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Size	Gift Message	Last Shipment	Received Shipments	Next Shipment
2	Large		B2	B1, S1, B2	S2
3	Large		B2	B1, S1, B2	S2
4	Large		B2	B1, S1, B2	S2
5	Large		B2	B1, S1, B2	S2			Up Coming Shipment
6	XLarge		B2	B1, S1, B2	S2
7	XLarge		B2	B1, S1, B2	S2				S1	S2	BRIEFS1	BRIEFS2
8	Small		B2	B1, S1, B2	S2			SMALL	60	17	34	90
9	Medium		B2	B1, S1, B2	S2			MEDIUM	101	10	55	121
10	Medium		B2	B1, S1, B2	S2			LARGE	101	18	33	107
11	Medium		B2	B1, S1, B2	S2			XL	42	6	9	29	TOTAL
12	Large		B2	B1, S1, B2	S2			TOTAL	304	51	131	347	833
13	Large		B2	B1, S1, B2	S2
14	Large		B2	B1, S1, B2	S2
15	Large		B2	B1, S1, B2	S2
16	Medium		B2	B1, S1, B2	S2
17	Large		B2	B1, S1, B2	S2
18	Large		B2	B1, S1, B2	S2
19	Medium		B2	B1, S1, B2	S2
Sheet1

Cell Formulas
Range	Formula
I8	=COUNTIFS($E$2:$E$748,"*S1*",$A$2:$A$748,"*Small*")
I9	=COUNTIFS($E$2:$E$748,"*S1*",$A$2:$A$748,"*Medium*")
I10	=COUNTIFS($E$2:$E$748,"*S1*",$A$2:$A$748,"*Large*")
I11	=COUNTIFS($E$2:$E$748,"*S1*",$A$2:$A$748,"*XLarge*")
I12	=SUM(I8:I11)
J8	=COUNTIFS($E$2:$E$748,"*S2*",$A$2:$A$748,"*Small*")
J9	=COUNTIFS($E$2:$E$748,"*S2*",$A$2:$A$748,"*Medium*")
J10	=COUNTIFS($E$2:$E$748,"*S2*",$A$2:$A$748,"*Large*")
J11	=COUNTIFS($E$2:$E$748,"*S2*",$A$2:$A$748,"*XLarge*")
J12	=SUM(J8:J11)
K8	=COUNTIFS($E$2:$E$748,"*B1*",$A$2:$A$748,"*Small*")
K9	=COUNTIFS($E$2:$E$748,"*B1*",$A$2:$A$748,"*Medium*")
K10	=COUNTIFS($E$2:$E$748,"*B1*",$A$2:$A$748,"*Large*")
K11	=COUNTIFS($E$2:$E$748,"*B1*",$A$2:$A$748,"*XLarge*")
K12	=SUM(K8:K11)
L8	=COUNTIFS($E$2:$E$748,"*B2*",$A$2:$A$748,"*Small*")
L9	=COUNTIFS($E$2:$E$748,"*B2*",$A$2:$A$748,"*Medium*")
L10	=COUNTIFS($E$2:$E$748,"*B2*",$A$2:$A$748,"*Large*")
L11	=COUNTIFS($E$2:$E$748,"*B2*",$A$2:$A$748,"*XLarge*")
L12	=SUM(L8:L11)
M12	=SUM(I12:L12)
E2	=IF(C2="","B1",IF(C2="B1","S1",IF(C2="S1","B2",IF(C2="B2","S2"))))
E3	=IF(C3="","B1",IF(C3="B1","S1",IF(C3="S1","B2",IF(C3="B2","S2"))))
E4	=IF(C4="","B1",IF(C4="B1","S1",IF(C4="S1","B2",IF(C4="B2","S2"))))
E5	=IF(C5="","B1",IF(C5="B1","S1",IF(C5="S1","B2",IF(C5="B2","S2"))))
E6	=IF(C6="","B1",IF(C6="B1","S1",IF(C6="S1","B2",IF(C6="B2","S2"))))
E7	=IF(C7="","B1",IF(C7="B1","S1",IF(C7="S1","B2",IF(C7="B2","S2"))))
E8	=IF(C8="","B1",IF(C8="B1","S1",IF(C8="S1","B2",IF(C8="B2","S2"))))
E9	=IF(C9="","B1",IF(C9="B1","S1",IF(C9="S1","B2",IF(C9="B2","S2"))))
E10	=IF(C10="","B1",IF(C10="B1","S1",IF(C10="S1","B2",IF(C10="B2","S2"))))
E11	=IF(C11="","B1",IF(C11="B1","S1",IF(C11="S1","B2",IF(C11="B2","S2"))))
E12	=IF(C12="","B1",IF(C12="B1","S1",IF(C12="S1","B2",IF(C12="B2","S2"))))
E13	=IF(C13="","B1",IF(C13="B1","S1",IF(C13="S1","B2",IF(C13="B2","S2"))))
E14	=IF(C14="","B1",IF(C14="B1","S1",IF(C14="S1","B2",IF(C14="B2","S2"))))
E15	=IF(C15="","B1",IF(C15="B1","S1",IF(C15="S1","B2",IF(C15="B2","S2"))))
E16	=IF(C16="","B1",IF(C16="B1","S1",IF(C16="S1","B2",IF(C16="B2","S2"))))
E17	=IF(C17="","B1",IF(C17="B1","S1",IF(C17="S1","B2",IF(C17="B2","S2"))))
E18	=IF(C18="","B1",IF(C18="B1","S1",IF(C18="S1","B2",IF(C18="B2","S2"))))
E19	=IF(C19="","B1",IF(C19="B1","S1",IF(C19="S1","B2",IF(C19="B2","S2"))))

 

[j_unsuitable]

Hey

just had a quick glance at it, but because of the "*large*" the Xlarge is included in that count as well, so Xlarge is counted twice, which would explain why the result is higher than the actual rows

Julian

 

[Dubyah]

Interesting, that's kind of a DUH moment.. Ill try adjusting the size names and let you know.

 

[Dubyah]

Works perfectly. Thank you Julian.

-Will