Question description:
This user has given permission to use the problem statement for this
blog.
Hey Folks, I need a formula that will take the value of an item if above or below a certain number, multiple it by the quantity in an adjacent column, and add it all together for me. Taking an inventory sheet that has varying values and quantity so I can divide everything above $5.01 and below $5 and multiply each by their quantity in the adjacent cell.
Solved by B. Q. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
03/06/2018 - 08:36
hello!
Excelchat Expert
03/06/2018 - 08:36
hello..
Excelchat Expert
03/06/2018 - 08:37
could you describe what do you want to achieve again?
User
03/06/2018 - 08:38
so I would like to get a formula that takes all values in column b
User
03/06/2018 - 08:38
above or below $5
User
03/06/2018 - 08:38
multiply each value by its quantity
User
03/06/2018 - 08:38
and add them together
User
03/06/2018 - 08:38
but I need to sort it by everything $5 and below, and $5.01 and above
Excelchat Expert
03/06/2018 - 08:39
ok...
User
03/06/2018 - 08:40
also a pivot table wouldn't work...as I have hundreds of these sheets spread out like this.... at least, I think a pivot table wouldn't work
User
03/06/2018 - 08:40
this is just a small cut and paste example
Excelchat Expert
03/06/2018 - 08:40
do you want to consider each case for below 5 and above 5?
User
03/06/2018 - 08:41
each row
User
03/06/2018 - 08:41
I don't care about the extended column
User
03/06/2018 - 08:41
but each row is its own item, that I need to group based upon its price
User
03/06/2018 - 08:41
then get a total value of inventory by multiply it by its quantity
Excelchat Expert
03/06/2018 - 08:42
in this example.. total inventory value is 62.45
User
03/06/2018 - 08:42
yes
Excelchat Expert
03/06/2018 - 08:42
so you want to have formula for getting 62.45
User
03/06/2018 - 08:42
but I need the separate values for all inventory above $5.01
User
03/06/2018 - 08:43
and below $5
Excelchat Expert
03/06/2018 - 08:43
ok.. i think i got what do you want..
User
03/06/2018 - 08:43
and then multiplied out by their quantities after separation
Excelchat Expert
03/06/2018 - 08:43
hold on please..
Excelchat Expert
03/06/2018 - 08:45
sumif will do what do you want..
User
03/06/2018 - 08:45
excelent
Excelchat Expert
03/06/2018 - 08:46
also you can change criteria
Excelchat Expert
03/06/2018 - 08:46
10
User
03/06/2018 - 08:47
how do I create these formulas without just cut and pasting them from you?
Excelchat Expert
03/06/2018 - 08:47
you need know how formula works..
Excelchat Expert
03/06/2018 - 08:48
let me explain step by step
User
03/06/2018 - 08:48
so I want to do this without referencing the C column
User
03/06/2018 - 08:48
is that possible?
Excelchat Expert
03/06/2018 - 08:48
Yes..
Excelchat Expert
03/06/2018 - 08:49
hold on please
Excelchat Expert
03/06/2018 - 08:52
you can use sumproduct with if
Excelchat Expert
03/06/2018 - 08:54
are you using this excel or google sheet?
User
03/06/2018 - 08:54
google sheet
Excelchat Expert
03/06/2018 - 08:54
then you can use this formula..
Excelchat Expert
03/06/2018 - 08:55
if you want to use this formula in excel.. it should be array formula..
Excelchat Expert
03/06/2018 - 08:55
do you understand how it works..?
Excelchat Expert
03/06/2018 - 08:56
do you want me to go through step by step how formula works..?
User
03/06/2018 - 08:56
no no, I understand the formula :)
User
03/06/2018 - 08:56
sumproduct is new and interesting and I will go google this and reverse engineer all of it
User
03/06/2018 - 08:57
I am getting it to work in my current parameters
Excelchat Expert
03/06/2018 - 08:57
google sheet and excel works slightly different way..
User
03/06/2018 - 08:57
thank you very much for your help :)
User
03/06/2018 - 08:58
I am seeing that with this
Excelchat Expert
03/06/2018 - 08:58
no problem..
User
03/06/2018 - 08:58
but I was able to rework it by retyping the formula in manually
User
03/06/2018 - 08:58
thank you again :)
Excelchat Expert
03/06/2018 - 08:58
no problem..
Excelchat Expert
03/06/2018 - 08:58
do you want to other problem to solve..??
Excelchat Expert
03/06/2018 - 09:02
i have changed little bit of sumif formula
Excelchat Expert
03/06/2018 - 09:04
do you want to work on other problem..??
Excelchat Expert
03/06/2018 - 09:05
Are you done with session..??
Excelchat Expert
03/06/2018 - 09:09
OK.. I think I will leave the session..
Excelchat Expert
03/06/2018 - 09:09
Have good day...
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.
