+ Reply to Thread
Results 1 to 14 of 14

Call Function of a total column?

  1. 03-27-2023, 01:44 PM #1
    cybid
    cybid is offline
    Registered User
    Join Date
    04-14-2021
    Location
    Prishtina, Kosovo
    MS-Off Ver
    365, 2019
    Posts
    9

    Post Call Function of a total column?

    Greetings,

    I work in a company where the sales reports are filled manually daily. I have managed to do a Sales Report Table on first sheet called RES and I have 31 more sheets with names S01,S02,S03....S31. By the end of night the copy all the information from the app and paste on the sheet depends on the day of month. Can you please guys help me how to call a function of the total amount (Gross) column because everyday it is not on the same cell of sheet.

    I will upload the content of a sheet just to see on what I am referring to.

    Thank you in advance.

    Test.xlsx
    Reply With QuoteRegister To Reply
  2. 03-27-2023, 01:51 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Call Function of a total column?

    Why, oh WHY have you made life 100x more difficult with all those merged cells!!!! They are the children of Satan.

    A VERY strong piece of advice.

    GET RID of them.

    Repost a file with 2 days of data, showing DIFFERENT rows as you described and (PREFERABLY) with NO merged cells.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 03-27-2023, 02:03 PM #3
    cybid
    cybid is offline
    Registered User
    Join Date
    04-14-2021
    Location
    Prishtina, Kosovo
    MS-Off Ver
    365, 2019
    Posts
    9

    Re: Call Function of a total column?

    Sorry but the program we use is a bit old and when we extract the sales reports does this merge which we can't do changes on export format....

    test3.xls
    Attached Files Attached Files
    Last edited by cybid; 03-27-2023 at 02:05 PM.
    Reply With QuoteRegister To Reply
  4. 03-27-2023, 02:35 PM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Call Function of a total column?

    Oh well...I'll take a look.
    Reply With QuoteRegister To Reply
  5. 03-28-2023, 06:23 AM #5
    cybid
    cybid is offline
    Registered User
    Join Date
    04-14-2021
    Location
    Prishtina, Kosovo
    MS-Off Ver
    365, 2019
    Posts
    9

    Re: Call Function of a total column?

    Greetings to all,

    Sorry to bother in such a short time, but is there any solution to my request?

    Thank you in advance,
    cybid
    Reply With QuoteRegister To Reply
  6. 03-28-2023, 06:58 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Call Function of a total column?

    I forgot about you!! Which Excel product do you need this to work with? 365 or 2019?
    Reply With QuoteRegister To Reply
  7. 03-28-2023, 08:25 AM #7
    cybid
    cybid is offline
    Registered User
    Join Date
    04-14-2021
    Location
    Prishtina, Kosovo
    MS-Off Ver
    365, 2019
    Posts
    9

    Re: Call Function of a total column?

    Hi Glenn,

    We are using 2019 version,

    Thnx
    Reply With QuoteRegister To Reply
  8. 03-28-2023, 08:53 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Call Function of a total column?

    I am struggling to find a convenient way to do this with O365 (I was hoping you might have it...).
    Reply With QuoteRegister To Reply
  9. 03-28-2023, 09:14 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Call Function of a total column?

    Got it....

    Set up a Named Range (called Rng, CTRL-f3 to view/edit):

    =INDEX(INDIRECT("'"&Result!$A13&"'!N15:N60"),MATCH("Gross",INDIRECT("'"&Result!$A13&"'!N15:N60"),0)):INDIRECT("'"&Result!$A13&"'!N60")

    Then use:

    =INDEX(Rng,AGGREGATE(15,6,ROW(Rng)-MIN(ROW(Rng))+1/(Rng=""),1)+1)

    copied down. See file.

    If the end of the data can occur below row 60, change the bits in red to something SENSIBLE... not 000s if you only have 0s.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 03-28-2023 at 11:15 AM.
    Reply With QuoteRegister To Reply
  10. 03-28-2023, 09:49 AM #10
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Call Function of a total column?

    there are two values "Gross"

    Column13 Column14
    Gross
    1.00
    1.00
    2.00
    Gross
    1.00
    1.00
    1.00
    3.00
    Gross
    1.00
    1.00
    1.00
    1.00
    Gross
    5.00
    5.00
    5.00
    Last edited by sandy666; 03-28-2023 at 10:06 AM.
    Reply With QuoteRegister To Reply
  11. 03-28-2023, 09:55 AM #11
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Call Function of a total column?

    And?? I know that and took it into account, starting the NR below the first one.
    Reply With QuoteRegister To Reply
  12. 03-28-2023, 10:07 AM #12
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Call Function of a total column?

    what OP want to return?
    from test3.xls i got in raw form

    Column13 Column14
    Gross
    1.00
    1.00
    2.00
    Gross
    1.00
    1.00
    1.00
    3.00
    Gross
    1.00
    1.00
    1.00
    1.00
    Gross
    5.00
    5.00
    5.00

    or like this
    Value
    Gross
    1.00
    1.00
    2.00
    Gross
    1.00
    1.00
    1.00
    3.00
    Gross
    1.00
    1.00
    1.00
    1.00
    Gross
    5.00
    5.00
    5.00
    Last edited by sandy666; 03-28-2023 at 10:13 AM.
    Reply With QuoteRegister To Reply
  13. 03-28-2023, 11:17 AM #13
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Call Function of a total column?

    LoL. I was on my phone for the last hour or so, and didn't spot that I hadn't attched the file. Hopefully it will be more obvious now. I returned the values of Gross from both sheets, that had been highlighted by the OP in yellow.
    Reply With QuoteRegister To Reply
  14. 03-29-2023, 08:24 AM #14
    cybid
    cybid is offline
    Registered User
    Join Date
    04-14-2021
    Location
    Prishtina, Kosovo
    MS-Off Ver
    365, 2019
    Posts
    9

    Re: Call Function of a total column?

    I want to thank you for the solution given to us.....

    It works fine so far.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. What function to use for 2 column with name and number for a total
    By 007Buff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-13-2020, 12:38 PM
  2. Need to get the total number of call raised
    By rvr ramana in forum Excel General
    Replies: 3
    Last Post: 08-29-2017, 05:17 PM
  3. [SOLVED] Sumif function to total column e if text in column a appears in more than one row
    By SMITH.CRYSTAL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2015, 03:58 PM
  4. Need statistical function. Want a total of each number in a column.
    By smjpl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2013, 10:07 AM
  5. Unable to total column after using vlookup function
    By trance_van in forum Excel General
    Replies: 1
    Last Post: 04-02-2009, 01:30 AM
  6. Function:total a colum using data from another column
    By RagDyer in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 05:05 AM
  7. [SOLVED] How do I total a column which was created using the MID function?
    By CindyB in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-10-2005, 02:06 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1