Hi. I have been delegated to write an Excel spreadsheet so as to auto collate
sales and remuniation for our community shop where we sell locally made goods
.. However I think I am out of my league and need help!
I have set up my first information spreadsheet as follows; Row "4"
Headings, Row "5" Start of data. Col "A" Blank, Col "B" Code number
relative to Col "C", eg "#1", Col "C" Names of the manufacture. eg "Fred" Col
"D" Blank, Col "E" Blank, Col "F" Code number reative to Col "G". eg "#1".
Col "G" List of products manufactured eg "Scarf", Col "H" Code number of the
manufacture as in Col "B" eg #1, Col "I" Cost of product. eg "$10.50"
Further, still on rows "4" for headings and comensing "5" empty cells down ,
in 5 column groups commensing at Col "J" Where at the end of each week I
insert into the subsiquant empty cells the quantity of each product sold for
that week or part week.
The formular I am after is as follows. On a separate sheet (But if is easier
it could be on the same sheet) I have in row 3 comensing at Col C and then
accross, the manufactures names linked by Offset formular to the first
sheet. And down column B commensing at row 5 the five monthly weeks by 12
months, again linked by offset formular to the previouse sheet. The formular
I need to fill these resulting cells should give me the TOTAL dollar and
cents amount resulting from the number of products sold multiplied by the
cost of the products. I could also use the offset formula to bring in the
quantity sold then multiply it by the corresponding cost of the product but
what happens if more than one of the manufactures products are sold in that
week. eg. If "Fred" who may have one or more products in column G indicated
by his identity number in column "H" sells In that week one or more multiple
products?
It's all getting way to complex for this old duck!
Also just to make thing a bit more difficult I need another formular to
transfer the quantity, product name and indifidual cost plus totals of all
products sold to a statement on a MONTHLY basis for each manufacture. All I
need here is a formular to bring the data over onto a row as the adition etc.
even I can work out.
I have looked at this problem all day and definatly need HELP.
Thanking you in advance if you can assist. I hope you can understand what I
am after. If not please do not give up I will reply with any info requested .
Derro

I think you just need to learn how to use SUMPRODUCT. do a searchfor
SUMPRODUCT on this website. For your case of fred

=Sumproduct(--(H1:H100="Fred"),I1:I100)

Thanks a lot Joel will do and will let you know how I go in a few days as I
am going away for 4 days and will study whilst away
Derro

"Joel" wrote:

I think you just need to learn how to use SUMPRODUCT. do a searchfor
SUMPRODUCT on this website. For your case of fred

=Sumproduct(--(H1:H100="Fred"),I1:I100)