Get a total price when product are marked with an X

[Konkol]

Hi,

i have a line of products that are marked with a numbers code, see example
above the numbers are the prices, some products have a cost faktor in the price
this means when the "870" is marked with an X the calculation should be b4xA2
same with the 801
for the other products like the A860 its just" when marked with X then give me the price above the product" in this case h2
for the products 85,95 and 96 the products are not marked but numered, so give me the number times the price of the product, so for 85 it's 2xm1
so i have 3 different way of calculating

i would need a formula that looks at the entire row (4) calculates when there is a number or X and gives me the sum of that in Z4

this is a very simplified example but it catches the gist of my problem, i cant get the (very long formula) to work without errors

is there any way to capure this is a formula? when the row is empty i need the total to be empty too

i hope you guys can help me, its a german excelsheet but an english formula is fine or an explenation on how to tackle this

thank you in advance

[deadlyduck]

I think SUMPRODUCT might help but I need to be 100% clear on the expected result.

Could you show a calculation using the sample data giving the expected result as the layout is a bit awkward and I'm finding that the explanation is difficult to understand.

Also,in cells M to O where you have the "nur neben..." text, can that text be moved to another row so that your worksheet only has either values or blanks in row 2? It would help with a solution.

[Konkol]

hi sure and ty for your trouble

the result would be
order 870; 43,72 x 3,1 = € 135,52
order 801; 14,57 x 3,5= € 51
order A860; 123,34 = € 123,34
order 85; 2x 67,03 = € 134,06
order 96; 4x 0,17 = € 0,68
order 2; 3,15 = € 3,15

Result € 447,75

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[deadlyduck]

I think that the following formula (not sure where it's supposed to go) gives the required result:

Please Login or Register  to view this content.

NB: IMPORTANT- the "nur neben" text must be swapped with the prices on row 1 i.e. ensure that row 2 just shows prices or blanks, not text for this to work.

[JohnTopley]

Minor change to Deadlyduck formula

Please Login or Register  to view this content.

Change rows 1 & 2 as per his note.

Would it not be easier just put a Factor with each product instead of "X" so in row 4 :

870 would have 3.1, A860 would be 1 and 65 would be 2

See attached row 11

[Konkol]

that would solve my problem, ill go have a chat with the department to see if we cant just nix the Faktor colum

just one more question, since there are 5000 rows, is there a way to autofill just a part of a formula?

so the part *(A13:V13) would be A14:V14 etc... is that possible? a google search left me puzzled

[JohnTopley]

I don't have 365 but I think it likely that the autofill can be done. Hopefully a 365 respondent will pick this up.