If cell contains this text, then perform formula from a different cell

[mmaslonka]

Hey guys! I'm trying to study some email analytics for the company I work for, and I want to compare days of the week. So I want to do a "If a cell in this column contains "Sunday", then include this specific cell next to it in this function." Does that make sense? Is that possible?

For example, the columns are currently organized by:
Title (A1), Date (B1), # sent (C1), # opened (D1), and more of miscellaneous data.
So I want to do something like if a cell in the B column contains "Sunday", say B12, B16, and B32, then I want to include c12, c16, and b32 in an average function.

[Glenn Kennedy]

Try this:

=AVERAGEIF(B2:B100,"Sunday",C2:C100)

[mmaslonka]

Thanks for your quick reply! Unfortunately it didn't work, I had a divide by 0 error.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[mmaslonka]

Sure! Here's a sample of it. If I built something wrong inside this sample I wouldn't be surprised - I'm no expert - so let me know if I need to fix anything else, too! I'll attached a desired result soon.

[Glenn Kennedy]

The formula you need is:

=AVERAGE(IF(WEEKDAY($B$3:$B$23)=1,$D$3:$D$23))

However, it still returns an error message as there are NO Sundays in that date range.

[Glenn Kennedy]

Oooops. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[mmaslonka]

Here is the same sheet, but with the example of the desired result below. My actual sheet has hundreds of rows - so if I can avoid manually doing it like this that would be fabulous.

[mmaslonka]

I think that worked for Sunday, but how do I change the day of the week? I tried switching the 1 after the weekday, thinking maybe Sunday was 1 and Monday might be 2, but that didn't work. Or is "Weekday" where I type the day of the week I'm looking for?

[AlKey]

Enter array formula in C27 and copy across and down

Please Login or Register  to view this content.

**Must be entered with Ctrl+Shift+Enter key combination.

[JohnTopley]

In C27

=IFERROR(AVERAGE(IF(WEEKDAY($B$3:$B$23)=ROWS($1:1),$C$3:$C$23)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down

the ROWS($1:1) will increment so match (Weekday(....)=1, then 2 etc

[mmaslonka]

Ah ha! I believe you have solved it! Thank you so much for your help!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.