+ Reply to Thread
Results 1 to 6 of 6

SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help required)

  1. #1
    Registered User
    Join Date
    04-10-2019
    Location
    Dubai
    MS-Off Ver
    2016
    Posts
    3

    Question SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help required)

    Hi, I have exported accounts data in excel from Tally software. Now stocks of the items are in Cartons (boxes); please help me, how to convert them into KG because record of all other items is on KG only. Please see attached excel sheet.

    I can get the sum of boxes for desired products but cannot convert them to KG in same formula. Currently I am using Vlookup for each product, maintaining separate sheet for each individual product, converting the boxes into units and then in KG. But number of sheets are too much in single work book.

    If we can use SUMIF to get sum/count of few items from big data, then there must be a way to get it multiplied with respective units.
    Attached Files Attached Files
    Last edited by KashifNawaz; 04-10-2019 at 01:50 PM.

  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
    79,416

    Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

    Try this:

    =SUMPRODUCT(Table1[Boxes],E$4:E$13,F$4:F$13)
    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
    04-10-2019
    Location
    Dubai
    MS-Off Ver
    2016
    Posts
    3

    Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

    Thanks Ali for your quick response.
    I tried this but sorry, its giving error. I have uploaded revised (more clear) sheet in my first post. Can you please check? Thanks

  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: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

    I think this will do it:

    =SUMPRODUCT(Table1[Boxes]*SUMIF(Table2[Filter Criteria],Table1[Product],Table2[KG/Box]))

  5. #5
    Registered User
    Join Date
    04-10-2019
    Location
    Dubai
    MS-Off Ver
    2016
    Posts
    3

    Re: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

    Wow.... You are awesome..... this worked like a charm.

    Thanks AliGW. much appreciated.

  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: SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula (help require

    You're welcome!

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

+ 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] Neither SUMIFS nor SUMPRODUCT deliver the required result
    By Gijs in forum Excel General
    Replies: 3
    Last Post: 07-11-2016, 05:25 AM
  2. Multiple stage multiplication in a single cell.
    By bubai in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2013, 06:04 AM
  3. Using Sumproduct or sumifs when a cell has a formula
    By cartica in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-15-2013, 04:02 PM
  4. [SOLVED] Nested formula required for two cell inputs and a single cell output
    By titch2k6 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-08-2013, 08:11 PM
  5. I need help setting up multiplication and Addition!
    By MikeJ1962 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-17-2013, 12:36 PM
  6. Multiplication/addition function
    By Trixie in forum Excel General
    Replies: 4
    Last Post: 01-27-2009, 03:26 PM
  7. addition to my date formula...required
    By Juco in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-30-2005, 08:06 AM

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