# Excel - IF Function Problem - Expert Solution

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

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.