"Contains" Formula

[Mike Copeland]

I want to total the amounts I spend for Breakfast, Lunch and Dinner
in restaurants. In column b18:b200 I have the amounts I've spent, and
in e18:e200 I have descriptions such as:
Breakfast
Dinner
Takeout Dinner
Dinner w/Terry & Cindy
Dinner w/Fallaws
Lunch
Takeout Dinner
Takeout Dinner
Lunch

I've tried the following formula, which produces values - but they're
wrong. Also, when I add a row with similar data nothing changes.
Please advise. TIA

=SUMIF(E18:E200,"Breakfast",B18:B200)

 

[Claus Busch]

Hi Mike,

Am Sun, 30 Mar 2014 10:27:17 -0700 schrieb Mike Copeland:

I want to total the amounts I spend for Breakfast, Lunch and Dinner
in restaurants. In column b18:b200 I have the amounts I've spent, and
in e18:e200 I have descriptions such as:

try:
=SUMPRODUCT(--(ISNUMBER(FIND("Dinner",E18:E200))),B18:B200)

Or create a Pivot Table

you can also look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Time_M"


Regards
Claus B.

 

[Claus Busch]

Hi Mike,

Am Sun, 30 Mar 2014 13:36:15 -0700 schrieb Mike Copeland:

Now, I'd like to modify this to add another
cell which produces the average. That is, if this formula is in H4, I'd
like to show its average in I4.

try:
=SUMPRODUCT(--(ISNUMBER(FIND(H17,$E$18:$E$200))),$B$18:$B$200)/SUMPRODUCT(--(ISNUMBER(FIND(H17,$E$18:$E$200)))*1)


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Mon, 31 Mar 2014 07:35:13 +0200 schrieb Claus Busch:

try:
=SUMPRODUCT(--(ISNUMBER(FIND(H17,$E$18:$E$200))),$B$18:$B$200)/SUMPRODUCT(--(ISNUMBER(FIND(H17,$E$18:$E$200)))*1)

the sum is in H4 then that is enough:
=H4/SUMPRODUCT(--(ISNUMBER(FIND(H17,$E$18:$E$200)))*1)


Regards
Claus B.

 

[Ron Rosenfeld]

I want to total the amounts I spend for Breakfast, Lunch and Dinner
in restaurants. In column b18:b200 I have the amounts I've spent, and
in e18:e200 I have descriptions such as:
Breakfast
Dinner
Takeout Dinner
Dinner w/Terry & Cindy
Dinner w/Fallaws
Lunch
Takeout Dinner
Takeout Dinner
Lunch

I've tried the following formula, which produces values - but they're
wrong. Also, when I add a row with similar data nothing changes.
Please advise. TIA

=SUMIF(E18:E200,"Breakfast",B18:B200)

Use wild card(s):

=SUMIF(E18:E200,"*Breakfast*",B18:B200)