+ Reply to Thread
Results 1 to 10 of 10

Formula based on several cells (if filled) and then using only a certain number multiplied

  1. #1
    Registered User
    Join Date
    05-22-2022
    Location
    Texas City, TX
    MS-Off Ver
    2016
    Posts
    30

    Formula based on several cells (if filled) and then using only a certain number multiplied

    I need a formula based on several cells

    I have 7 cells, most of the time not all 7 will be used in a 12 hour shift
    B14
    J14
    R14
    Z14
    AH14
    AP14
    AX14

    In those cells the numbers are 54000 or higher, but I only want 52000 of each cell to be tabulated into the BN12 cell (tonnage)
    Once you get the 52000 for each completed cell, then it's needs to be multiplied by 0.0011 to determine the total tonnage.

    For example, if 3 of the 7 cells are used. B14, J14, R14
    Then I need it to take 52000x3 which is 156000 times 0.0011 for BN12 to show 171.6 tons

    And if 4 of the 7 cells are used, B14, J14, R14, Z14, then that's 208000 x 0.0011 for a total of 228.8 tons in BN12

    Or I could do 57.2 tons per completed cell for the tonnage in the B12 cell.

    I hope I'm clearly explaining this, I appreciate any feedback.
    Thanks everyone.

  2. #2
    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
    80,410

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    There are instructions at the top of the page explaining how to attach your sample workbook.

    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.
    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.

  3. #3
    Registered User
    Join Date
    05-22-2022
    Location
    Texas City, TX
    MS-Off Ver
    2016
    Posts
    30

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    Spreadsheet attached, I couldn't figure out how to only attach a partial sheet.
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    Maybe this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    05-22-2022
    Location
    Texas City, TX
    MS-Off Ver
    2016
    Posts
    30

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    I tried using =SUMPRODUCT(--(LEN(B14:AX14)>0))
    But it included the contents of other cells within the range
    I only need it to only check the following cells B14, J14, R14, Z14, AH14, AP14, AX14
    I then tried to use the click on each cell by using CTRL and it looked like this
    =SUMPRODUCT(--(LEN(B14,J14,R14,Z14,AH14,AP14,AX14)>0))
    I got an error that said You've entered too many arguments for this function
    I will keep researching and watching YouTube videos

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    Or even:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-22-2022
    Location
    Texas City, TX
    MS-Off Ver
    2016
    Posts
    30

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    Thanks TMS
    Works like a charm!

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,598

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    deleted......
    Last edited by protonLeah; 08-27-2022 at 04:57 PM.
    Ben Van Johnson

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Formula based on several cells (if filled) and then using only a certain number multip

    You're welcome.

+ 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. Replies: 1
    Last Post: 04-15-2020, 06:59 PM
  2. Display Number of Times a Number is Multiplied in a Formula
    By visualnotsobasic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2018, 01:36 PM
  3. [SOLVED] capture name of cell based on the number of filled cells
    By countryfan_nt in forum Excel General
    Replies: 3
    Last Post: 04-18-2017, 05:00 AM
  4. [SOLVED] Multiplied macro starts based number of selected rows
    By vendam in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2017, 02:44 PM
  5. [SOLVED] Carry down formula for columns based on the number of rows filled with data
    By shre0047 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-05-2016, 10:18 AM
  6. vba to check if answer from 2 cells with =int(rand()*number) multiplied together is equal
    By marshall_massive in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-18-2013, 06:33 AM
  7. Return number of cells filled
    By LMB in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-28-2005, 10:07 PM

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