Need Function

zahidrock

New Member
I have two sheet in one excel file, column A, B, C available on sheet 1 and G, H available on sheet two, i want to get sum on sheet 2 price section, but on sheet1 A column have multiple name of one, so that i need to use it for Vlookup formula, but with Vlookup formula not give me sum price on sheet2.

i can also do it with SUMIF function but i also need one thing that when i put OUT on Sheet1 C column on that time i need to automatically remove that price on sheet2 on H Column.

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

skywriter

Well-known Member
I made a mistake so I removed the post.

skywriter

Well-known Member
Try this in H2 and copy down.

Code:
``=SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$25=\$G3),Sheet1!\$B\$2:\$B\$25,--(Sheet1!\$C\$2:\$C\$25<>"out"))``

Last edited:

zahidrock

New Member
Try this in H2 and copy down.

Code:
``=SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$25=\$G3),Sheet1!\$B\$2:\$B\$25,--(Sheet1!\$C\$2:\$C\$25<>"out"))``

skywriter

Well-known Member

Sumproduct multiplies the array of numbers inside of it together and then adds them all up.

If the first array of numbers equals 123 and the second equals 456, you get 1*4 + 2*5 +3*6 and the answer is 32.

The first part looks at the range A2 to A25 and anything that equals G3 gets a true and everything that doesn't gets a false. The -- turns the trues and falses into 1's for the trues and 0's for the falses.

The middle part grabs all the dollar amounts from B2 to B25.

The last part is similar to the first part it takes everything that doesn't have the word out and turns it into trues and everything that does is a false they then get converted to 1's and 0's.

Then all the ones and zeros are multiplied. Remember 0 times anything always equals zeros. So for the cells that meet the first criteria you get a 1 times the dollar amount times a 1 if it doesn't say out and then they are all added together.

It's very similar to the way you as a person would look at the problem. Does this equal the criteria, yes, is the word out next to it no, okay so I'll use this number and you would just do that for all the numbers and add them up. Pretty much the same thing here.

Last edited:

Replies
4
Views
801
Replies
5
Views
874
Replies
9
Views
458
Replies
3
Views
218
Replies
24
Views
1K

1,195,651
Messages
6,010,932
Members
441,574
Latest member
Prescience

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back