Results 1 to 3 of 3

Thread: using Sumif and left with tables

  1. #1

    using Sumif and left with tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello

    I'm facing a problem using sumif at the same time as left function.
    Resuming, i have three tables, where for both column A is contract. Table 1 is ammount of sales, table 2 is quantities the area occupied for generating such sales, and table 3 need to do the calculation Sales/Area.

    The problem is that some contract might have some exceptions and therefore, either for sales and area they are treated independently, but for table 3 they such be considered as the total ammount for the area occupied.

    Example:
    Table1 - Sales
    Contract|Sales
    1000 2500
    1000_1 3000

    Table2 - Area
    Contract|Area
    1000 800m2
    1000_1 0

    Table3 - Sales per Area
    Contract|SalesArea
    1000 Should be (2500+3000) / 800m2

    I was tryng to achieve the result by using (sumif(table1Contract;left(table3contract;4);Table1Sales)/(sumif(table2Contract;left(table3contract;Table2Area) but i'm not getting the desired result.

    I looked on the forum and they're recomending sumifs/sumproduct but i did not get how to use it.

    Either 3 tables are to be expanded, so i would need something that was not "hardcoded" for a string of contract.

    Can anyone help me?

    Many thanks in advance
    Pedro

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    May try:

    sumif(table1Contract;table3contract&"*";Table1Sales)/(sumif(table2Contract;table3contract&"*";Table2Area)

    the &"*" concatenates a wildcard to the table2Contract so that it will look at all contracts that begin with those characters.


  3. #3
    Thanks a lot NBVC

    It works perfectly!!!

Posting Permissions

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