Excel - COLUMN Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.