Hi,
I am creating a table with nested if statements. The idea is to generate sales data based on products and day wise. The left column is date of month. All products are transposed at top row. I will input the quantity of each product infront of date below that product. After those products are done then I have below formula to generate cost, and revenue.
The data table is on another sheet. I have created if statement but I want all true statements to sum up. Below is the statement:
=IF(B2>0,VLOOKUP(B1,Data,2,FALSE)*B2,IF(C2>0,VLOOKUP(C1,Data,2,FALSE)*C2,IF(D2>0,VLOOKUP(D1,Data,2,FALSE)*D2,IF(E2>0,VLOOKUP(E1,Data,2,FALSE)*E2,IF(F2>0,VLOOKUP(F1,Data,2,FALSE)*F2,IF(G2>0,VLOOKUP(G1,Data,2,FALSE)*G2,IF(H2>0,VLOOKUP(H1,Data,2,FALSE)*H2,IF(I2>0,VLOOKUP(I1,Data,2,FALSE)*I2,IF(J2>0,VLOOKUP(J1,Data,2,FALSE)*J2,IF(K2>0,VLOOKUP(K1,Data,2,FALSE)*K2,IF(L2>0,VLOOKUP(L1,Data,2,FALSE)*L2,IF(M2>0,VLOOKUP(M1,Data,2,FALSE)*M2,IF(N2>0,VLOOKUP(N1,Data,2,FALSE)*N2,IF(O2>0,VLOOKUP(O1,Data,2,FALSE)*O2,IF(P2>0,VLOOKUP(P1,Data,2,FALSE)*P2,IF(Q2>0,VLOOKUP(Q1,Data,2,FALSE)*Q2,IF(R2>0,VLOOKUP(R1,Data,2,FALSE)*R2,IF(S2>0,VLOOKUP(S1,Data,2,FALSE)*S2,IF(T2>0,VLOOKUP(T1,Data,2,FALSE)*T2,IF(U2>0,VLOOKUP(U1,Data,2,FALSE)*U2,IF(V2>0,VLOOKUP(V1,Data,2,FALSE)*V2,IF(W2>0,VLOOKUP(W1,Data,2,FALSE)*W2,IF(X2>0,VLOOKUP(X1,Data,2,FALSE)*X2,IF(Y2>0,VLOOKUP(Y1,Data,2,FALSE)*Y2,0))))))))))))))))))))))))
Is there any way to sum up all the Vlookup functions together. Is there any other way to summarise the statement that will be more handy as I need it copy this formula.
P.S. I am not excel master. I am still learning.
Thanks
Bookmarks