+ Reply to Thread
Results 1 to 6 of 6

Nested if statements and their sums

  1. #1
    Registered User
    Join Date
    12-13-2021
    Location
    Pk
    MS-Off Ver
    2016
    Posts
    3

    Nested if statements and their sums

    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

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Nested if statements and their sums

    Please yellow banner at top of the page on how to attach a sample workbook - include examples of expected results.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    12-13-2021
    Location
    Pk
    MS-Off Ver
    2016
    Posts
    3

    Re: Nested if statements and their sums

    Here is the sample
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Nested if statements and their sums

    In Z2:

    =SUMPRODUCT(TRANSPOSE(($B2:$Y2>0))*Data[Cost])
    Attached Files Attached Files
    Last edited by AliGW; 12-13-2021 at 04:57 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    12-13-2021
    Location
    Pk
    MS-Off Ver
    2016
    Posts
    3

    Re: Nested if statements and their sums

    Thank you AliGW. This seems good

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Nested if statements and their sums

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multible if statements with different cells and different criteria and different sums
    By Wolfieee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2019, 09:36 PM
  2. questions with if statements and nested if statements
    By Pat Excel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-03-2013, 01:41 PM
  3. [SOLVED] Nested Sums
    By HJII in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2013, 05:15 PM
  4. [SOLVED] Nested IF Sums
    By Notters in forum Excel General
    Replies: 2
    Last Post: 08-21-2012, 07:19 AM
  5. [SOLVED] Nested if sums
    By Notters in forum Excel General
    Replies: 2
    Last Post: 07-25-2012, 09:42 AM
  6. Nested if sums
    By Notters in forum Excel General
    Replies: 5
    Last Post: 08-20-2010, 01:27 PM
  7. Replies: 12
    Last Post: 05-15-2009, 08:38 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1