Sum If Function Needed: Rows w/ Text, Need Total in Numbers

[lalaland999]

Hi! I'm trying to use Excel to meal plan (weird, I know).

Anyway, here's what the spreadsheet looks like:

What I'm trying to do is a If Sum function I believe. Each day, I need a tally of the number of colors (green, purple, red, yellow, blue, orange and TSP). The column will contain text and not numbers. So, if on Day 1, I want to eat apples 3 times that day (all in the purple row), the total (which doesn't appear in this image - it's at the bottom of the 5 sections) should be 3.

Does that make sense? I can clarify. Essentially, I want a total # for every row that has text in it. (I'm a novice so go easy on me!) I've tried a few different Sum If functions to no avail. If someone could provide me with the appropriate function, I would appreciate it! Thanks in advance.

 

[AhoyNC]

Maybe either the COUNTIFS or SUMPRODUCT formula below will work for you.
Changes ranges to match your data.

Excel Workbook
	A	B	C	D
1		Day 1
2	Green	Cant.
3	Purple	Shak.
4	Red
5	Yellow
6	Blue
7	Orange
8	TSP
9
10	Green
11	Purple	apple
12	Red
13	Yellow	Oat
14	Blue
15	Orange
16	TSP
17
18	Totals
19	Green	1		1
20	Purple	2		2
21	Red	0		0
22	Yellow	1		1
23	Blue	0		0
24	Orange	0		0
25	TSP	0		0
Sheet

 

[lalaland999]

Thank you so much for that! That seems to work. Follow up question - not sure if this would work. If I wanted two items in one cell - say, apples and pears...if I use a comma to separate, will Excel let me parse those out as two answers vs. one?

 

[AhoyNC]

There's probably a better way, but this should work.
It is an array formula and must be entered with

Excel Workbook
	A	B	C
2	Green	Cant.
3	Purple	Shak.
4	Red
5	Yellow
6	Blue
7	Orange
8	TSP
9
10	Green
11	Purple	apple, pear
12	Red
13	Yellow	Oat
14	Blue
15	Orange
16	TSP
17
18
19	Totals
20	Green	1
21	Purple	3
22	Red	0
23	Yellow	1
24	Blue	0
25	Orange	0
26	TSP	0
CTRL-SHIFT-ENTER.

 

[lalaland999]

That worked perfectly! Thank you so much!