Thread: Calculate a value per a code inserted

To get the sum of each column:

C25: =SUMPRODUCT(SUMIF($A$5:$E$9,C$18:C$24,$C$5:$G$9))
copied to E25

In terms of colouring the resulting values - see Conditional Formatting:

http://www.contextures.com/xlCondFormat01.html

Did you read the prior post at all ?

It's all rather too vague to offer you a tailored solution but if we assume for the sake of demo. that your 3 columns list is situated in Sheet2 in Range A1:C1000 and that your "criteria" list of IDs is on Sheet1 in C$18:C$24 as before then:

Sheet1!C25:
=SUMPRODUCT(SUMIF(Sheet2!$A$1:$A$1000,C$18:C$24,Sheet2!$C$1:$C$1000))

If you need more specific assistance I'm afraid we need more specific info.

Ok, my first Idea was understand how the formulas should go with the small catalogue. Here is more info, I made my catalogue based on three suppliers having Code, Item, Condition, Price, the Catalogue is already in next sheet, in first sheet we would only have the calculator, which would be the two colums asosiating the code of the item with the price o it, and results.

The document is attached,

By the way, thanks for all the support previously, I see you are an expert, I will follow what you say.

Your layout (Catalogue) isn't ideal - always better whenever & wherever possible to use contiguous data tables
(ie one table for all suppliers - add a column to the table to denote supplier for each record)

Given your present setup:

C15:
=SUMPRODUCT(SUMIF(Catalogue!$A$4:$K$242,C$8:C$14,Catalogue!$D$4:$N$242))
copied to E15

Originally Posted by wcedeno

I see you are an expert, I will follow what you say.

Above avg perhaps but no expert - don't follow what I say blindly - we all make mistakes.

Thanks again, i works fantastic, just a question what would be the step to follow if I would like to to add more items to each supplier and still be calculated in the Formula.

Thanks

You will be best served explaining your requirements via a sample file - as it stands I'm afraid I don't understand the question.

Originally Posted by DonkeyOte

You will be best served explaining your requirements via a sample file - as it stands I'm afraid I don't understand the question.

How is it posible you cant understand it? It's so clear, Ok. I will attach a sample. the new items are in yellow.

Thanks

I had assumed you meant something other than change the cell references (change K242 to be Kn and N242 to be Nn where n is the last row of data)

If you intend to change the range all the time either
a) setup some excess capacity (eg K500/N500)
else
b) use a Dynamic Range but given use of SUMPRODUCT I would suggest you create with an INDEX based RefersTo approach rather than OFFSET but there are more examples of the latter.