**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi, I need some help setting up a formula that takes info from my 'Purchase Log' sheet so it updates my 'Totals By Media Channel' sheet
The formula first needs to identify whether Column I reads 'Google' or 'Media Alpha' of my Purchase Log, and then I need it to SUM up everything that says 'Google' in Column I and then take that total and multiply it by 12 x 1.5 x .8 so it then updates my Totals by Media Channel sheet.
I feel like it should be the 'IF' function version of this formula:
=(SUM('Purchase Log'!H2:H39))*(12)*(1.5)*(0.8)

Solved by G. J. in 37 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
16/03/2018 - 01:54

Hi there

User
16/03/2018 - 01:54

Hi!

Excelchat Expert
16/03/2018 - 01:54

How are you and how may i assist you with today?

User
16/03/2018 - 01:54

I need some help setting up a formula that takes info from my 'Purchase Log' sheet so it updates my 'Totals By Media Channel' sheet The formula first needs to identify whether Column I reads 'Google' or 'Media Alpha' of my Purchase Log, and then I need it to SUM up everything that says 'Google' in Column I and then take that total and multiply it by 12 x 1.5 x .8 so it then updates my Totals by Media Channel sheet. I feel like it should be the 'IF' function version of this formula: =(SUM('Purchase Log'!H2:H39))*(12)*(1.5)*(0.8)

Excelchat Expert
16/03/2018 - 01:55

can you please share me a sample worksheet,

Excelchat Expert
16/03/2018 - 01:55

that would be easier for me to evaluate your requirement

User
16/03/2018 - 01:56

I don't think I'm allowed to do that.

User
16/03/2018 - 01:56

Is there any way you can give me the IF version of the formula I have above?

User
16/03/2018 - 01:57

but Column H needs to be analyzed in its entirety

Excelchat Expert
16/03/2018 - 01:57

okay will surely help, but it would have been great if you could have randomize your data and share a sample

Excelchat Expert
16/03/2018 - 01:57

so for this you would need sumifs formula

User
16/03/2018 - 01:58

Okay, can you please tell me how to duplicate the sheet and then randomize?

User
16/03/2018 - 01:58

okay great

Excelchat Expert
16/03/2018 - 01:58

formula would be like =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

User
16/03/2018 - 01:59

Okay, can you please create it with this info for me please?

User
16/03/2018 - 01:59

(SUM('Purchase Log'!H2:H39))*(12)*(1.5)*(0.8)

Excelchat Expert
16/03/2018 - 01:59

like you have two criteria "Google" and "Media alpha"

Excelchat Expert
16/03/2018 - 02:00

as i can see from your formula you referencing two sheets

User
16/03/2018 - 02:01

Yes, I am

Excelchat Expert
16/03/2018 - 02:01

so to create the exact formula i would suggest you to share the sheet

Excelchat Expert
16/03/2018 - 02:01

dont worry its confidential and its safe to share here

User
16/03/2018 - 02:02

I can't do that. It has purchase data in it.

User
16/03/2018 - 02:02

Even if it's randomized and even fake info I think I would still get in trouble

User
16/03/2018 - 02:02

if not fired

Excelchat Expert
16/03/2018 - 02:03

hhaa hhaa sure i understand

Excelchat Expert
16/03/2018 - 02:03

okay let me see how i can convey you the formula

Excelchat Expert
16/03/2018 - 02:04

can you see the shared sheet?

User
16/03/2018 - 02:04

I cannot. The firewalls here prevent us from using google drive and docs

Excelchat Expert
16/03/2018 - 02:05

oopps, then it would quite tough

Excelchat Expert
16/03/2018 - 02:05

so i will try rephrasing the formula you shared

Excelchat Expert
16/03/2018 - 02:05

using sumifs

User
16/03/2018 - 02:06

Okay, thank you

Excelchat Expert
16/03/2018 - 02:07

so basically there would be two sum values, right?

User
16/03/2018 - 02:07

Yes, from the same column

Excelchat Expert
16/03/2018 - 02:07

1 for "Google" and the other for "Media Alpha"

User
16/03/2018 - 02:08

Yes.

Excelchat Expert
16/03/2018 - 02:08

great

Excelchat Expert
16/03/2018 - 02:09

and the sum range is 'Purchase Log'!H2:H3 in purchase sheet, right?

User
16/03/2018 - 02:10

The purchase log will be added to constantly, so it'll probably be more like 150+

User
16/03/2018 - 02:10

H2:H155

Excelchat Expert
16/03/2018 - 02:11

okay, these values would be in currency format, right?

User
16/03/2018 - 02:11

Yes

Excelchat Expert
16/03/2018 - 02:13

and in which column you have "google" and "Media Alpa" classification?

User
16/03/2018 - 02:13

Column I

Excelchat Expert
16/03/2018 - 02:13

Range?

User
16/03/2018 - 02:14

I2:I155

Excelchat Expert
16/03/2018 - 02:14

sheet name?

User
16/03/2018 - 02:14

Purchase Log

User
16/03/2018 - 02:15

I'll need the same formula for Google and Media Alpha because the Google one will need to update the Google Row on my Totals Sheet, and the Media Alpha one will be updating the Media Alpha Row on my totals sheet

User
16/03/2018 - 02:16

Pretty much the same formula but switched respectively

Excelchat Expert
16/03/2018 - 02:17

okay this formula would be for google =sumif('Purchase Log'!I2:I155,"Google", 'Purchase Log'!H2:H155)*(12)*(1.5)*(0.8)

User
16/03/2018 - 02:17

You're a god damn saint!

Excelchat Expert
16/03/2018 - 02:18

that was quite difficult for me, to blindly figure out the formula

User
16/03/2018 - 02:19

Thank you!

Excelchat Expert
16/03/2018 - 02:19

Though thanks for appreciating, please do a valuable rating for me

Excelchat Expert
16/03/2018 - 02:19

that would work out for me

User
16/03/2018 - 02:19

Of course!

User
16/03/2018 - 02:19

But when I plug it in, the numbers are off

Excelchat Expert
16/03/2018 - 02:20

similarly for Media Alpha, just replace media alpha with google

Excelchat Expert
16/03/2018 - 02:20

can you share a screenshot though

Excelchat Expert
16/03/2018 - 02:20

using snipping tool

User
16/03/2018 - 02:22

I really can't

Excelchat Expert
16/03/2018 - 02:22

=sumif('Purchase Log'!I2:I155,"Google", 'Purchase Log'!H2:H155)*12*1.5*0.8

Excelchat Expert
16/03/2018 - 02:22

try this once

User
16/03/2018 - 02:23

It's giving me the same number as before

Excelchat Expert
16/03/2018 - 02:23

like what number

User
16/03/2018 - 02:23

$5,023.87

Excelchat Expert
16/03/2018 - 02:24

yes that might be correct as your explanation, for google

Excelchat Expert
16/03/2018 - 02:25

like its 1st summing up the google values and then multiplying with 12, 1.5 and 0.8

Excelchat Expert
16/03/2018 - 02:25

isn't that what you wanted?

User
16/03/2018 - 02:25

yes, that's what I need. Maybe there's a mistake in the sheet elsewhere.

User
16/03/2018 - 02:25

I'm sure you're right.

Excelchat Expert
16/03/2018 - 02:26

yes please check the sheet names both in sheet and in formula, thats only upto you

User
16/03/2018 - 02:26

You're right!

User
16/03/2018 - 02:26

Where's that rating 5 Stars for you!!

Excelchat Expert
16/03/2018 - 02:27

okay okay i got it, wait

Excelchat Expert
16/03/2018 - 02:27

=sumif('Purchase Log'!I2:I155,"Google", 'Purchase Log'!H2:H155)*(12*1.5*0.8)

Excelchat Expert
16/03/2018 - 02:27

i guess you should bracket the numeric multiplication as a whole

Excelchat Expert
16/03/2018 - 02:28

the Rating option will be after you end the session, Sir/Maam

Excelchat Expert
16/03/2018 - 02:28

May i now your name

User
16/03/2018 - 02:28

Matt

User
16/03/2018 - 02:28

Thank you so much!

Excelchat Expert
16/03/2018 - 02:29

nice to e-meet you Matt

User
16/03/2018 - 02:29

Hahahaha you as well! Have a great day!

Excelchat Expert
16/03/2018 - 02:29

it was really nice talking to you

Excelchat Expert
16/03/2018 - 02:29

have a good day to you too

User
16/03/2018 - 02:29

You too, thank you! What was your name again!

User
16/03/2018 - 02:29

?

Excelchat Expert
16/03/2018 - 02:30

Sudha

Excelchat Expert
16/03/2018 - 02:30

way from India

User
16/03/2018 - 02:30

Thanks Sudha! Have a great day!

User
16/03/2018 - 02:30

That's where the majority of our company is. Tell them over at EXL I said hello!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*