Stock Check - Excel times 'Quantity' against 'Price' to have one total?

Hello everyone, and thanks for any help given.

I am doing a stock check on Excel for a small business I work for.

Basicaly it looks like this, although with hundreds of entries.

Supplier Product Colour Quantity Price per unit (VAT inc)

Chairs ltd Chair Brown 4 £23
Tables ltd Table Black 1 £34


Total stock price
£126

If you haven't guessed, I want a total for the prices although this would mean a formula to include all quantities, for example the brown chair total would be £92 not £23, I want the whole stock cost in one field.

Absolute Excel novice. Appreciate any help, thanks.

Hello,
Does the attached do what you want??

Regards, galvinpaddy

You can use the SUMPRODUCT() function for that:

Like this: =SUMPRODUCT(D2:D35,E2:E35)

adjust the range references to match your actual data (but make sure the row numbers are the same for each range)

Originally Posted by galvinpaddy

Hello,
Does the attached do what you want??

Regards, galvinpaddy

Yes it seems so, although you have added an extra field (adding the quantity & PPUwhich I do not have - although I would be now interested in having this. You haven't explained how I do this?

Also while I'm posting, my profile says I use Excel 2003 but it's actually 2007 - changed from my school days! Whats the simple way of making the whole text smaller?!

Apologies for stupid questions, complete novice on Excell.

Thanks for all the help and so fast, brilliant.

Originally Posted by Cutter

You can use the SUMPRODUCT() function for that:

Like this: =SUMPRODUCT(D2:D35,E2:E35)

adjust the range references to match your actual data (but make sure the row numbers are the same for each range)

Might frustrate you but I don't really understand the formula.

For information purposes, my quantity starts from D8, and PPU is E8.

I could upload the file if it helps?

Thanks again.

Adjust the formula to:

SUMPRODUCT(D8:D35,E8:E35) but change the two 35's to the last row number of your ranges.

The formula (as the name implies) sums the products. In this particular formula it multiplies D8 by E8, then D9 by E9, then D10 by E10 and so on until it reaches the last row of the ranges. It then sums all of those multiplication results.

Originally Posted by Cutter

Adjust the formula to:

SUMPRODUCT(D8:D35,E8:E35) but change the two 35's to the last row number of your ranges.

The formula (as the name implies) sums the products. In this particular formula it multiplies D8 by E8, then D9 by E9, then D10 by E10 and so on until it reaches the last row of the ranges. It then sums all of those multiplication results.

That is precisely what I want, i'll have a play and hopefully it should work - you will hear from me soon! lol.

Does that only work typed in manually?

I don't know what you mean.

I only had a limited time in the office the other day on my work computer, I copied and pasted your formula and it didn't seem to work, I was wondering if I had to type the formula manually in the box to work? I have an hour tomorrow morning to complete this so thought I best check up.

Thanks for the help. I have not touched Excel since my school days.

I have not touched Excel since my school days.

That doesn't say anything - you may have graduated last month!

No, you don't have to manually enter the formula. It's better to copy/paste because it eliminates any chance of typos (unless of course I, or any other responder, made the typo - but that NEVER happens!). I pretty much always paste in the formula bar (with the destination cell selected).

What do you mean by "it didn't seem to work"? Wrong result? Error? No result?

Almost eight years ago I left school.

It just seemed to paste the formula into the cell, although I didn't paste it into the formula bar, just the cell itself.

Is the cell formatted as text?

I wouldn't know, how would I find this out?

Copy the formula from the formula bar, got to the next cell, right click, format cells, set it as general, click ok, paste the copied formula into the formula bar

Cheers, if only I had the document on me now - will be on it tomorrow morning!

Thanks, worked perfectly. Think I was being an idiot the other day!

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Hope it works fine for you, have all your questions now been answered?