Need Function

[zahidrock]

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.

 

[skywriter]

I made a mistake so I removed the post.

 

[skywriter]

Try this in H2 and copy down.

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

 

[zahidrock]

Try this in H2 and copy down.

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

Thanks bro, you provide that function what i want.. thanks again, if you can please explain about this function

 

[skywriter]

Thanks bro, you provide that function what i want.. thanks again, if you can please explain about this function

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.