+ Reply to Thread
Results 1 to 19 of 19

Calculate field based on multiple columns and rows

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Calculate field based on multiple columns and rows

    Hi All,

    I am trying to calculate the value from a table based on multiple rows and columns. the attached file has the data in the 'Closed' tab. The criteria is as follows:

    1) Closed column A = SEAU OS A1
    2) Closed column B = In Home
    3) Closed column C = TAT
    4) Closed column D = Total
    5) Closed row 1 = SEAU OS B1

    as an example, taking INFINET for A1 and week 30 (AE1), the returned value should be 13.5

    I have tried various iterations of SUMIFS, SUMPRODUCT and no luck.

    please help!!!

    cheers

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Calculate field based on multiple columns and rows

    Hi and welcome to the forum

    I am unble to open your file, it says it me be corrupt. Can you try to upload again please?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    hopefully now it works

    cheers

  4. #4
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Calculate field based on multiple columns and rows

    Quote Originally Posted by Fade-e View Post
    hopefully now it works

    cheers
    Still doesn't work

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Calculate field based on multiple columns and rows

    Nope same problem...

    ...excel cannot open the file KPI-CTAT-test.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file...

  6. #6
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    im using 2010 if that makes a difference?

    it opens for me when I download it :S

    cheers

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Calculate field based on multiple columns and rows

    im on 2007 but that should not make a difference

  8. #8
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    im at work at the moment so don't even have access to dropbox or other file sharing site

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Calculate field based on multiple columns and rows

    not all members can access those kind of file hosting sites (company firewalls and stuff). And anyway, I doubt its the upload process that is the problem, we upload dozens of files daily here.

    Is there any way you can put together a dummy file based on your data?

  10. #10
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    one last try otherwise will have to wait till I get home.

    thanks for trying this far

    cheers

  11. #11
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    see if this works
    Attached Files Attached Files

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Calculate field based on multiple columns and rows

    the 3rd try of your file still did not work, but the book1 file did

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Calculate field based on multiple columns and rows

    Based on the data in your dummy file, and the the criteria you set (and I know you want them cumulative, but this is to illustrate), these are the cou8nts of the individual criteria. So if you want those criteria compounded, you will always get zero as an answer because there is nothing for the 1st criteia

    SEAU OS A1...0
    In Home...192
    TAT...280
    Total...210
    SEAU OS B1...0


    Perhaps you could include what your expected outcome would be and how you arrived at that?

  14. #14
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    ok, using book 1

    on Sheet1, cell A1 will have a drop down list of clients, e.g., INFINET SERVICE SOLUTIONS PTY LTD

    so from sheet3,

    1) column A has the client listed (cells A387-A434 is INFINET SERVICE SOLUTIONS PTY LTD)
    2) I then want to look at only the 'In Home' jobs (cells B387-B398)
    3) then only 'TAT' (Turn Around Time) (cells C395-C398)
    4) then the 'Total' (cell D395)
    5) then the week number (in Sheet 1 it would be B3-BB3 and the data is in Sheet3 A1-xx1)

    so if we were talking about week 30 (cell Sheet1 AE3), then result would be 13.5 (Sheet3 AH398)

    hope that makes sense

    cheers

  15. #15
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    and apologies I forgot to add the week numbers in Sheet 1 (cells B3-BB3)

    when I referred to SEAU OS A1, I meant the tab called 'SEAU OS' cell A1. same for the other SEAU OS.

    cheers

  16. #16
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    I should add that I am trying to get the formula so that it doesn't need adjusting each wee as more data come through. so next week the data will go to week 31, then the week after week 32 will be added, etc.

    I tried

    =SUMIFS(Sheet3!1:1048576,Sheet3!A:A,A1,Sheet3!B:B,"In Home",Sheet3!C:C,"TAT",Sheet3!D:D,"Total",Sheet3!1:1,B2)

    but get an #VALUE error

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Calculate field based on multiple columns and rows

    Quote Originally Posted by Fade-e View Post
    and apologies I forgot to add the week numbers in Sheet 1 (cells B3-BB3)

    when I referred to SEAU OS A1, I meant the tab called 'SEAU OS' cell A1. same for the other SEAU OS.

    cheers
    ok so did you upload an updated workbook?

  18. #18
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    updated file attached
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    07-25-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculate field based on multiple columns and rows

    no one able to assist?

    cheers

+ 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] Calculate Points based on Max Value in Rows and Columns
    By dksodhi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2013, 01:30 PM
  2. Replies: 4
    Last Post: 09-18-2012, 06:41 PM
  3. Average multiple rows based on 2 columns
    By beaner in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-19-2011, 09:18 PM
  4. Replies: 1
    Last Post: 05-18-2009, 11:21 AM
  5. sum multiple rows based on progressing date field on each row
    By Thane in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-02-2006, 01:15 PM

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