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.
