Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: count total among several tables
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default count total among several tables

    Hi,

    I have a workbook with several sheets. The example used below is very simplified

    Each sheet of the workbook has several tables. Each table is composed the same way but there might be more or less names in the tables per sheet. For example sheet #1 might have 4 people. Sheet #2 might have 6 etc. (in reality there are many more and each sheet has 10 tables: all the same people but different topics: I used "rental" - "short lease" - "long lease" for name sake. The next sheet has also those 10 tables but might have a shorter or longer name list.

    Per sheets I need to count per person for how many items they are on target (100% or above), for how many they are off target (100% or below).

    I have tried with countif but selecting each list of name and match it with the % below or above results in an tremendous formula.

    In addition the name in the reference table is slightly off from the result table (a feature that cannot be changed right now). For example the reference table might have J. Smith - S. Smith. The table where you count the result per name states John Smith - Susan Smith. I know how to get around this error by using "*"& &"*" and then I can change those few people manually. That however is the least of my worries.

    Counting how many or on target and off target through and excel formula would be a tremendous help.

    Any input would be greatly appreciated.


    The last table is the result I am looking for - basically for which I would like to create a formula - right now I just entered it manually


    Rental Short Lease Long Lease
    Column1 Actual Goal % Column1 Actual Goal % Column1 Actual Goal %
    John 5 4 125% Rose 5 3 167% Linda 8 6 133%
    Mark 4 4 100% Linda 4 3 133% Rose 7 6 117%
    Rose 3 4 75% Mark 3 3 100% Mark 5 6 83%
    Linda 2 4 50% John 2 3 67% John 3 6 50%
    14 16 350% 14 12 467% 23 24 383%
    Off Target On Target Total Items
    John 2 1 3
    Linda 1 2 3
    Mark 1 2 3
    Rose 1 2 3
    Last edited by Vcoppens; Aug 15th, 2019 at 09:02 PM.

  2. #2
    New Member
    Join Date
    Apr 2019
    Location
    Porto - Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    Hi there,

    at first sight it will be much easier if you convert the last table into something like this:

    Off target Off target Off target On target On target On Target Total Items
    Rental Short Lease Longe Lease Rental Short Lease Longe Lease
    John
    Linda
    Mark
    Rose

    Of course you can merge the "Off target" and "On Target" cells.
    With this change you can set each cell to go for that specific sheet and return the value that you want.

    Will be reasonable to you, to transform the table?

    Hope I could help.

  3. #3
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    May be this.

    Excel 2013/2016
    ABCDEFGHIJKLMN
    1RentalShort LeaseLong Lease
    2NameActualGoal%NameActualGoal%NameActualGoal%
    3John54125%Rose53167%Linda86133%
    4Mark44100%Linda43133%Rose76117%
    5Rose3475%Mark33100%Mark5683%
    6Linda2450%John2367%John3650%
    7
    8NameOff TargetOn TargetTotal Items
    9John213
    10Linda123
    11Mark123
    12Rose123

    Sheet5



    Worksheet Formulas
    CellFormula
    B9=IF(VLOOKUP(A9,$A$2:$D$6,4,0)<100%,1,0)+IF(VLOOKUP(A9,$F$2:$I$6,4,0)<100%,1,0)+IF(VLOOKUP(A9,$K$2:$N$6,4,0)<100%,1,0)
    C9=IF(VLOOKUP(A9,$A$2:$D$6,4,0)>=100%,1,0)+IF(VLOOKUP(A9,$F$2:$I$6,4,0)>=100%,1,0)+IF(VLOOKUP(A9,$K$2:$N$6,4,0)>=100%,1,0)
    D9=SUM(B9:C9)

    Last edited by Sam_D_Ben; Aug 16th, 2019 at 03:16 AM.
    Sam_D_Ben

  4. #4
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,931
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    Another option, by using Post #.03 table,

    [B9] =COUNTIFS($D$3:$N$6,"<1",$A$3:$K$6,$A9)

    [C9] =COUNTIFS($D$3:$N$6,">=1",$A$3:$K$6,$A9)

    [D9] =B9+C9

    All formulas copied down

    Regards
    Bosco

  5. #5
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    Thank you very much for your replies!
    I will be testing them out over the weekend and see which was best fitted to extract what I needed.

    Thank you in advance!

  6. #6
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    Hi every one!
    @tico_ocit: thank you for the suggestion to change the table - unfortunately they want to keep the layout as it is.

    @Sam_D_ Ben and @bosco_yip: these were 2 wonderful formula suggestions. I tested them on my simplified spreadsheet and both worked as a charm. I will implement at work on Monday and see which one needs the least manipulation: both source and final result are in 2 different workbooks.

    Thank you to all for your great insights - now ready to enjoy my weekend. Hope yours will be great too.

  7. #7
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    Hi Bosco, I have a small question: in my example I showed 3 tables. The remaining tables are sorted below (i.e. 3 tables from row 1 thru 6, the next 3 from row 7 thru row 12,...)

    Holding ctrl to go to the next rows gives an error in my formula ...

    Would you be able to help with this?

    Thank you in advance

    COUNTIFS($D$3:$N$6,$D$10:$N$13),"<100%",($A$3:$K$6,$A$10:$K$13,$A23)

  8. #8
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    edited: there are 10 tables in total sorted in the sheet as follows
    3 tables
    3 talbes
    3 tables
    1 table and to the right, on the same row level, a table with a different layout so a drag down till table 10 doesn't really work because of the non-similar table to the right of it ...

    --- Dragging down works! Even if the table to the right is not in the same layout as the others - sorry for the bother
    Last edited by Vcoppens; Aug 17th, 2019 at 05:56 PM. Reason: found a solution

  9. #9
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    Quote Originally Posted by Vcoppens View Post
    COUNTIFS($D$3:$N$6,$D$10:$N$13),"<100%",($A$3:$K$6,$A$10:$K$13,$A23)
    May be something like this.

    =COUNTIFS($D$3:$N$13,"<1",$A$3:$K$13,$A23)
    Sam_D_Ben

  10. #10
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count total among several tables

    Hi @Sam D Ben, thank you for your suggestion. On my test workbook I was able to drag completely down to the 10th table (basically including the irrelevant table to the right of this last table. I will test it tomorrow at work and see if it works there too.
    Kind regards,

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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