Formula for retuning a value of 4 critera

[reddaze]

Hi there, newbie so please be gentle!

I have a sheet which is a report of daily sales accross a range of products with sales value and salesperson amongst other information.

In simple terms, this is what I am trying to do:

Column A is a nine digit product part number.
Column B is a five character salesman code
Column C is the value of said product in column A
Column D is the qty sold of said product in A

Is there a formula I can use, to show the total value of a specific product part number sold by a particluar salesman?

Thanks

[Jonmo1]

Try

=SUMPRODUCT(--(A$1:A$100=F2),--(B$1:B$100=G2),C$1:C$100,D$1:D$100)

F2 = Part number in question
G2 = salesman in question

[reddaze]

excel.jpg

thanks for your response, but it doesnt appear to solve it??

Picture attached to show issue

Regards

[Jonmo1]

Put quotes around Bobby...

"Bobby"

[reddaze]

Brilliant thank you.

1 more thing, if I want that result to appear on sheet 2, (with the data on sheet 1) how do I modify the formula to look at sheet 1.

=SUMPRODUCT(--(Sheet1!A$2:A$100=111222333),--(sheet!B$2:B$100="BOBBY"),Sheet1!C$2:C$100,sheet!D$2:D$100)

doesn't appear to do the job??

[Jonmo1]

You've misspelled a couple of the sheet names, look closely.
2 of them are Sheet1, the other 2 are just Sheet

[reddaze]

working perfect now thanks - just 1 more question - if I didnt want to include the qty side of it, how do i go about that?

thanks

[Jonmo1]

You're welcome.

To not include the qty part, just remove the qty range (column D)
=SUMPRODUCT(--(A$1:A$100=F2),--(B$1:B$100=G2),C$1:C$100)

[reddaze]

doh! Didnt realise it really was 'that' simple

Thank you again

[reddaze]

Just one more thing with this one, how can I use the same formula but to show qtys of part numbers sold starting with 111 for each salesman? Is this possible? Thanks in advance

[reddaze]

Hi again,

Going back to my OP;

Column A is a nine digit product part number.
Column B is a five character salesman code
Column C is the value of said product in column A
Column D is the qty sold of said product in A

Using this data above, how I can I return back the total value of a product by salesman where the product code ends in a specific number only?

[sktneer]

Something like this.....

To get the total value for a particular salesman (bobby in this case) for product code starting with 111, try this.....

Please Login or Register  to view this content.

To get the total value for a particular salesman (bobby in this case) for product code ending with 333, try this.....

Please Login or Register  to view this content.

[reddaze]

Wow thanks!

Also, getting complicated now though, how about a formula to show the total sum of the product sold where the first three digits and the last digit are odf a certain criteria?

[oeldere]

Then i give you another advice.

Look at the oppotunity of a pivot table.

Your structure of data is perfect for working with an pivot table.

[reddaze]

Yes I think that would be easier, but....

the actual data used is updated daily and will be used at various sites.

Other sites do not have the ability to use pivot tables.

Do you have another method??

thank you

[oeldere]

Please Login or Register  to view this content.

I don't understand that one.

Maybe you can make an table of your data.

If you add new data in the next empty row below the table, the table will automatic expand.

In that case you only have to refresh the pivottable.

[reddaze]

this spreadsheet will be sent to other sites to uses. the people at these sites, are not very good at using excel. This is why I need a simple spreadheet with just formulas.

thank you