My brain is jamming a bit to start the day.
I'm trying to make a formula that given one cell, checks if that , counts how many times other cells in that row are >1.
So for example,
A1 = Bob [or whatever name you want to enter]
A2 = Counts how many different ingredients were purchased by person in A1 for their salad
Col C: People (list of people and their salad purchases (all unique))
Col D: Number of croutons purchased
Col E: Number of bell peppers purchased
Col F: Number of tomatoes purchased
So I would like to enter Bob or Anne or Joe in cell A1, and then cell A2 will spit out how many different ingredients the person in A1 purchased. So if they purchase 20 croutons, no bell peppers and 2 tomatoes, cell A2 will show "2" since they purchased croutons and tomatoes.
So I know I can do this in a not-so-elegant way (since C is unique):
A2 = COUNTIFS(C:C,"="&A1,D:D,">0") + COUNTIFS(C:C,"="&A1,E:E,">0") + COUNTIFS(C:C,"="&A1,F:F,">0")
But I'm wondering if I can somehow use an AND or COUNTIFS or something else to combine this all into one formula. More like (though I know this doesn't work)
A2 = COUNTIFS(C:C,"="&A1,COUNTIFS(D:F,">0"))
Which would be a great help since in reality I have a ton more ingredients to count (say like 50 different ingredients).
On Excel for Mac 2011
Thanks folks!
I'm trying to make a formula that given one cell, checks if that , counts how many times other cells in that row are >1.
So for example,
A1 = Bob [or whatever name you want to enter]
A2 = Counts how many different ingredients were purchased by person in A1 for their salad
Col C: People (list of people and their salad purchases (all unique))
Col D: Number of croutons purchased
Col E: Number of bell peppers purchased
Col F: Number of tomatoes purchased
So I would like to enter Bob or Anne or Joe in cell A1, and then cell A2 will spit out how many different ingredients the person in A1 purchased. So if they purchase 20 croutons, no bell peppers and 2 tomatoes, cell A2 will show "2" since they purchased croutons and tomatoes.
So I know I can do this in a not-so-elegant way (since C is unique):
A2 = COUNTIFS(C:C,"="&A1,D:D,">0") + COUNTIFS(C:C,"="&A1,E:E,">0") + COUNTIFS(C:C,"="&A1,F:F,">0")
But I'm wondering if I can somehow use an AND or COUNTIFS or something else to combine this all into one formula. More like (though I know this doesn't work)
A2 = COUNTIFS(C:C,"="&A1,COUNTIFS(D:F,">0"))
Which would be a great help since in reality I have a ton more ingredients to count (say like 50 different ingredients).
On Excel for Mac 2011
Thanks folks!