# Posts by sobi_256

thank you so much to both of you and answering my queries

thank you for the reply, just for uderstanding, why the formula works if i use<> , the only difference between the two formulas is that <> is missing

Hello

i basically want to sum A,G, and D S from column A, my sum range is in column G, sorry my formulas are wong in the test file. but i still getting the same issue

Code
1. =SUMIFS(G:G,A:A,"A",A:A,"G",A:A,"D S")

i did use the below formula in cell k1 and this works, but the above formula does not. i will reupload the file again with the correction in formulas. I just wanna know where i am going wrong because if above formula is wrong then the below one shouldn't work as well

Code
1. =SUMIFS(G:G,A:A,"<>A",A:A,"<>G",A:A,"<>D S")

## Files

• TEST.xlsx

Hello Guys

Can someone pls explain why the formula works in k1 but not in k2, if anyone has the answer or a different formula pls lemme know

Regards

sobi

## Files

• TEST.xlsx

thankyou all , really appreciate the help

thankyou i think i have got it, i used from your previous code

Code
1. Function Nb2Let(ColumnNumber As Long) As String
2. Nb2Let = Split(Cells(1, ColumnNumber).Address, "\$")(1)
3. End Function
4. For i = 2 To lr
5. Cells(i, 15) = Evaluate("=(" & Nb2Let(11) & i & "*" & Nb2Let(12) & i & "*" & Nb2Let(13) & i & "*" & Nb2Let(14) & i & ")/1000000")
6. Next i

this gives an error #VALUE! :(, do evaluate work over a range?

i just cannot figure out the syntax to use evaluate

Hello Guys

i have a following line

Code
1. lr = Cells(Rows.Count, "B").End(xlUp).Row
2. Range("U1:U" & lr).Formula = "=(K1*L1*M1*N1)/1000000000"

how can i change this to worsksheet function kind of thing which will not show the formula and only show the desired answers

Regards

hmm but i want in to be dynamic , so is the below code the right syntax

Code
1. Cells(i, lCol + 6) = Evaluate("=SUM(INDEX(A:" & lCol & ",0,Match(Cells(i,1Col+5),\$1:\$1,0)))")

thank you so much, really appreciate it, one more thing if i do not need formula to be show, like an evanluate of something how would i do that, like it still calculates the value but instead of showing a formula in cell it should show the answer

O2 is dynamic as well like it can be any column which will be 1col+5 i was gonna use a loop thats why i wanted o2 to be dynamic

Code
1. Cells(i, lCol + 6).Formula = "=SUM(INDEX(A:" & lCol & ",0,MATCH(O2,\$1:\$1,0)))"

so this o2 should be cells(i,1Col+5), i hope i am making it clear

Hello Guys

i am stuck with the syntax of the formula

this works fine

Code
1. Cells(2, lCol + 6).Formula = "=SUM(INDEX(A:J,0,MATCH(O2,\$1:\$1,0)))"

but i want the above formula to be dynamic and i do not know what the proper syntax will be, i want A:J to be replaced to A:1Col and match (O2) to be replaced with match(cells(i,1Col+5))

Code
1. Cells(i, lCol + 6).Formula = "=SUM(INDEX(A:J,0,MATCH(O2,\$1:\$1,0)))"

Thankyou guys

thank you everyone, appresiate the help

just out of curiosity if the data is big, how would u do it then
thanks

thank you so much, i was making it too complex by using auto filters and then copy pasting

Hello Guys

i am attacking an excel sheet and i am looking for a vba solution.

On database sheet i have products and in each product different items are used.

On orders sheet i have the product name and quantity in column A and B respectively.

i am looking for a vba solution that can give me an output like shown in column J to S.

Column L has the total quantity which multiply the oder quanity with the quantity shown in database

ORDERS.xlsx

Regards

Thanks

for example 1 shirt is made up of 3 individual components
components qty

25 X 23 X 1220 7

100 X 85X 450 8

50 X 35 X 567 9

so if i order 10 shirts the the outcome will be

components qty

25 X 23 X 1220 70

100 X 85X 450 80

50 X 35 X 567 90

1 shoe is made up of 2 individual components

components qty

LACE BLACK 5

LACE WHITE 6

so if i order 5 shoes the out put sheet should have
components qty

LACE BLACK 25

LACE WHITE 30

i hope i am explaning itself clearly