Counting instance in a column, COUNTIF not working for it

This is a very difficult problem to solve with formulas the way you are organizing your data (maybe not even feasible). The best way to enter this type of data is to have one row for each combination of name/size/color, with the quantity for the combination:

Name	Size	Color	Quantity
Fred	XL	BK	2
Fred	XL	BL	1
Janet	M	RD	1

Then use formulas or a pivot table to determine any needed results, like how many of each size/color to order.

See my attached sheet and see if it is something like what you are trying to do. The formula that determines how many BLACK shirts (or any other color with a change of notation) is:

Formula:

Please Login or Register  to view this content.

Note: It is an ARRAY formula, entered by pressing CTRL + SHIFT + ENTER, instead of just enter.

- Moo

Amazing, That worked perfect Moo! Would you mind telling me why it worked? I can't quite understand what it is doing.

Edit: Think i figured it out. It is counting the characters in the range, minus the characters that aren't "BK", then divided by the length of "BK" to give me a total instance number. Now that is thinking outside the box!

SUM(LEN($B$2:$F$7) <-- Totals up all characters in range
LEN(SUBSTITUTE($B$2:$F$7,"BK","")) <-- Total of all characters after removing desired color code, in this case "BK"
/LEN("BK") <--- divide by length of desired string, in this case 2

So you are totaling up all characters in the range, then subtracting the total characters left after removing the desired color code.

I hope that makes sense?

- Moo