Forum Discussion

thesspie_ann's avatar
thesspie_ann
Copper Contributor
Jan 18, 2018

Excel formula assistance needed!

Hi all, 

 

I'm a bit of a novice when it comes to excel formulas, so any assistance I can get on the below would be amazing!

 

I need a formula that does the following: 

 

  • On 1 sheet I have a table of numbers
    • The column headings contain a 3-digit job number (eg. D558 - General Tasks)
  • Underneath each column heading there are rows titled ‘Total income’ and ‘Total cost of sales’
  • On sheet 2 there is:
    • A column with all the 3-digit job numbers from Sheet 1
    • A column with the heading ‘Total income’
    • A column with the heading ‘Total cost of sales’

 

I need formulas that fill the ‘total income’ and ‘total cost of sales’ columns in Sheet 2,

 

With the information in these corresponding cells in Sheet 1.

 

EG.

 

Sheet 1:

Sheet 1

Sheet 2:

Sheet 2

Let me know if any more info is required. 

 

Thank you!

Formulas & Functions
training

4 Replies

  • Willy Lau's avatar
    Willy Lau
    Iron Contributor
    Jan 19, 2018

    I am not good at Excel.  For this question, I can think of array formula solution only.

     

    For Total Cost of Sales

    =INDEX('Sheet1'!$B$6:$D$21, 2116, MATCH(TEXT('Sheet2'!$A2,"000"), MID('Sheet1'!$B$6:$D$6, 2, 3), 0))

    where $D should be changed to the last column in your sheet1.

     

    After typing the above formula, keep in the editing cell mode, press CTRL + SHIFT + ENTER to make it as a array formula. Then it will look like

    {=INDEX('Sheet1'!$B$6:$D$21, 2116, MATCH(TEXT('Sheet2'!$A2,"000"), MID('Sheet1'!$B$6:$D$6, 2, 3), 0))}

     

    For Total income

    =INDEX('Sheet1'!$B$6:$D$21, 127, MATCH(TEXT('Sheet2'!$A2,"000"), MID('Sheet1'!$B$6:$D$6, 2, 3), 0))

    remember to turn it to array formula.

     

     

    Array formula has low performance issue.  If you data set is large, it performs slowly.

     

    edition: correction is made in orange colour.

    • thesspie_ann's avatar
      thesspie_ann
      Copper Contributor
      Jan 19, 2018

      Hi Willy, thanks so much for the reply!

       

      I tried putting the formulas into the spreadsheet, but it comes up with #REF! when I click control + shift + enter. 

       

      For total cost of sales, I edited the titles used so it looks like; 

       

      =INDEX(ProfitLoss!$B$6:$FE$21, 21, MATCH(TEXT(Sheet1!$A2,"000"), MID(ProfitLoss!$B$6:$FE$6, 2, 3), 0))

       

      The name of sheet 1 is actually 'ProfitLoss' and sheet 2 'Sheet1'. 

       

      The last column is the spreadsheet is FE. 

       

      I wasn't sure whether to change where it has '000' to the digits of the job number (eg. '570'), 

       

      But when I did this it didn't give me the right number either. 

       

      Any suggestions to where I've gone wrong?

       

      Thanks!

      • Willy Lau's avatar
        Willy Lau
        Iron Contributor
        Jan 19, 2018
        the 2nd parameter for index formula should be

        7 for total income
        16 for total cost of sales

        I made mistakes before, sorry.

Resources