"Contains" Formula

M

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)
 
C

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.
 
C

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.
 
C

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.
 
R

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top