+ Reply to Thread
Results 1 to 31 of 31

SUMIFS/AVERAGEIFS across multiple columns

  1. #1
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    SUMIFS/AVERAGEIFS across multiple columns

    Hello again Forum!!

    i have sheet 1 which contains 3 charts of 3 teams. often agents will work with other teams and occasionally all 3.

    i need to SUMIFS agents hours across all 3 teams (Sheet 2)

    and AVERAGEIFS agents % across all 3 teams (Sheet 2)

    as well as by job as you will see on sheet 2, but i may be able to get that from "agents" formula.

    Any help is greatly appreciated, and ty guys very much for past help!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Hello
    Maybe the formulas (green cells) in the attached reply will do as you require?

    DBY
    Attached Files Attached Files

  3. #3
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    the total columns (B and H) are perfect! ty very much!

    The Average % columns (C and I) are not giving accurate average. Maybe because of blanks, just a guess...
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,000

    Re: SUMIFS/AVERAGEIFS across multiple columns

    how about a pivot table.

    see the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    This wouild be a LOT easier/simpler, if you had 1 column with ALL names in it, then 1 column for Total, and 1 for %
    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

  6. #6
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    i know it would FDibbins... unfortunately i did not make the original, and too late now... been telling boss to let me handle projects from start to finish to avoid these problems...

  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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Understood, been there, done that

    OK here is how I would handle this. Add 6 extra helper columns to sheet 2 (could get messy, if this extends to more columns on sheet1)
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    Agent Total Hours Total %
    2
    Hours
    %
    Hours
    %
    Hours
    %
    3
    Bill R
    12987
    1.73%
    0
    0.00%
    0
    0.00%
    12987
    1.73%
    12987
    1.73%
    4
    Chris F
    1078
    1.46%
    0
    0.00%
    0
    0.00%
    1078
    1.46%
    1078
    1.46%
    5
    Eric B
    17145.3
    9.17%
    17145.3
    9.17%
    0
    0.00%
    0
    0.00%
    17145.3
    9.17%
    6
    Erin F
    0
    0.00%
    0
    0.00%
    0
    0.00%
    0
    0.00%
    0
    7
    Jarod B
    19054.5
    15.31%
    8752.5
    5.04%
    10302
    10.27%
    0
    0.00%
    19054.5
    7.66%
    8
    Joseph D
    781.5
    0.91%
    0
    0.00%
    0
    0.00%
    781.5
    0.91%
    781.5
    0.91%
    9
    Kent D
    1940.5
    2.32%
    0
    0.00%
    1940.5
    2.32%
    0
    0.00%
    1940.5
    2.32%
    10
    Kevin D
    10526
    3.25%
    7371.5
    1.84%
    0
    0.00%
    3154.5
    1.41%
    10526
    1.63%
    11
    Mike C
    2351.5
    1.39%
    0
    0.00%
    2351.5
    1.39%
    0
    0.00%
    2351.5
    1.39%
    12
    Mitch N
    0
    0.00%
    0
    0.00%
    0
    0.00%
    0
    0.00%
    0
    13
    Paul W
    12628.5
    2.54%
    7466
    1.31%
    5162.5
    1.23%
    0
    0.00%
    12628.5
    1.27%
    14
    Stephen C
    0
    0.00%
    0
    0.00%
    0
    0.00%
    0
    0.00%
    0
    15
    Steven M
    28676.25
    9.34%
    28676.25
    9.34%
    0
    0.00%
    0
    0.00%
    28676.25
    9.34%
    16
    William G
    6201.5
    2.28%
    0
    0.00%
    6201.5
    2.28%
    0
    0.00%
    6201.5
    2.28%

    D3=SUMIF(Sheet1!$B$3:$B$24,Sheet2!$A3,Sheet1!D$3:D$24)
    copied down and to column E
    F3=SUMIF(Sheet1!$G$3:$G$24,Sheet2!$A3,Sheet1!I$3:I$24)
    copied down and to column G
    H3=SUMIF(Sheet1!$L$3:$L$24,Sheet2!$A3,Sheet1!N$3:N$24)
    copied down and to column I
    J3=SUM(D3,F3,H3)
    K3=IFERROR(AVERAGEIFS(E3:I3,$E$2:$I$2,"%",E3:I3,">0"),"")
    both copied down.

    You cah hide the helper columns

  8. #8
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    but if i SUM each chart, would that not give me a bigger average.. i need average for each agent/Code

    but helper columns have gave me some ideas.

  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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    It is totalling each column for each "table", and it then calcs the average of the 3 respective helper columns - but only if they are greater than 0.

    If you want, instead, to calc the average % for each table, then change that column's SUMIF() to AVERAGEIF(). Note though, that if you then take the average of the 3 helper columns, that will be mathematically incorrect, as you cannot take an average of averages.

    How are those %'s arrived at, anyway?

  10. #10
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Not average of each table... sorry im horrible at explainins as you allready know lol...

    i need the average of each agent/code... the table itself doesn't really come in to play with what is needed now. its just the way the source data is set up.
    i need to do something like =AVERAGEIFS or =SUMIFS with =COUNTIFS (i think).

    it is conversion % of Sales/Leads

  11. #11
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    something like this maybe? for cell C7... but too few of arguments

    =SUMIF(Sheet1!$B$3:$B$22,A7,Sheet1!E$3:E$22)+SUMIF(Sheet1!$G$3:$G$22,A7,Sheet1!J$3:J$22)+SUMIF(Sheet1!$L$3:$L$22,A7,Sheet1!O$3:O$22)/COUNTIFS(Sheet1!$B$3:$B$22,A7,Sheet1!E$3:E$22)+COUNTIFS(Sheet1!$G$3:$G$22,A7,Sheet1!J$3:J$22)+COUNTIFS(Sheet1!$L$3:$L$22,A7,Sheet1!O$3:O$22)

  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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    so, you want to get the average of each name (lets stick with that for now), for each table?
    If so, then using my same table above...
    E3=IFERROR(AVERAGEIF(Sheet1!$B$3:$B$24,Sheet2!$A3,Sheet1!D$3:D$24),"")
    G3=IFERROR(AVERAGEIF(Sheet1!$G$3:$G$24,Sheet2!$A3,Sheet1!I$3:I$24),"")
    I3=IFERROR(AVERAGEIF(Sheet1!$L$3:$L$24,Sheet2!$A3,Sheet1!N$3:N$24),"")
    J3=SUM(D3,F3,H3)
    K3=IFERROR(AVERAGEIFS(D3:I3,$D$2:$I$2,"Hours",D3:I3,">0"),"") (this 1 is not correct, I need to work more on it)

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Enter in B3 and fill down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter in C3 and fill down: It is not clear if you want a total or an average of the averages. This is the total of the averages.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter in C3 and fill down: This is the average of the averages
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter in H3 and fill down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter in I3 and fill down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Format the % columns as Percentage and the other columns as General.
    Last edited by newdoverman; 06-24-2015 at 06:56 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  14. #14
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    yes for each name (will be colomn C)

    and then for each code (will be column I)

  15. #15
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Very close Newdoverman...
    Column B and H are correct!! TY Sir!

    but when i did =Average to check column C and I... they are off..

    No idea why...

    i updated example to show this.
    Attached Files Attached Files

  16. #16
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS/AVERAGEIFS across multiple columns

    This formula will average the percentages disregarding the blank cells in the Percent columns:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I don't know what values you expect in column I but I can't find an error in my calculation.
    Attached Files Attached Files

  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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    What answer are you expecting as the average for, say, jarod B, for the average, and how do you arrive at that?

  18. #18
    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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    hmm maybe Im missing something here, but if you want the average hours for each name in that table, then, using NDM's smart formula in B3...
    C3=B3/COUNTIF(Sheet1!$B$3:$O$24,A3)

    This will take the sum of all values for a name/the count of all entries for that name

  19. #19
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    yes, NDB hours formula is perfect.

    Average % is what giving problems...

    to test the formulas (jarod B, =AVERAGE(Sheet1!E20,Sheet1!E13,Sheet1!J19,Sheet1!J14,Sheet1!J10,Sheet1!J7) Which gave me 2.55%
    I uploaded a new example a couple posts back, but i dont think it upload correctly


    Eric B 1.88%

    Jarod B 2.55%

    CD 1.39%

    NP 1.56%

    NX 1.15%

  20. #20
    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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Adjusting NDM's formula...
    =SUMPRODUCT(--(Sheet1!$B$3:$L$24=$A3),Sheet1!$E$3:$O$24)/COUNTIF(Sheet1!$B$3:$O$24,A3)

    Note that this is still mathematically incorrect. To be accurate, you would need to sum the values that make up the %, and do the calc on those sums

  21. #21
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS/AVERAGEIFS across multiple columns

    @Ford
    On the chance that he didn't want to have the averages include the blanks I resorted to this which changed some of the values:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula for column I:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Values for Eric B greater than 0:
    1.66%
    1.40%
    1.95%
    2.11%
    2.05%
    _____
    9.17%

    Average
    1.83%
    Attached Files Attached Files

  22. #22
    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
    51,883

    Re: SUMIFS/AVERAGEIFS across multiple columns

    This is for jarob b...
    T
    U
    4
    0.0138
    5
    0.017
    6
    0.0299
    7
    0.0166
    8
    0.0553
    9
    0.0205
    10
    Total
    0.1531
    11
    Count
    6
    12
    Average
    0.025517


    It is still not accurate though, averages of averages are not correct

  23. #23
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Works great! TY so much guys!
    one more thing... can you add an =IFERROR please
    i tried lol, but this formula is overwhelming... and had no success as still have errors

    =IFERROR(SUMPRODUCT(--(Sheet1!$A$3:$A$24=Sheet2!$A4)*(Sheet1!$E$3:$E$24))+SUMPRODUCT(--(Sheet1!$A$3:$A$24=Sheet2!$A4)*(Sheet1!$J$3:$J$24))+SUMPRODUCT(--(Sheet1!$A$3:$A$24=Sheet2!$A4)*(Sheet1!$O$3:$O$24))+SUMPRODUCT(--(Sheet1!$A$3:$A$24=Sheet2!$A4)*(Sheet1!$T$3:$T$24))+SUMPRODUCT(--(Sheet1!$A$3:$A$24=Sheet2!$A4)*(Sheet1!$Y$3:$Y$24)),"")/IFERROR(COUNTIFS(Sheet1!$A$3:$A$24,Sheet2!A4,Sheet1!$E$3:$E$24,">0")+COUNTIFS(Sheet1!$A$3:$A$24,Sheet2!A4,Sheet1!$J$3:$J$24,">0")+COUNTIFS(Sheet1!$A$3:$A$24,Sheet2!A4,Sheet1!$O$3:$O$24,">0")+COUNTIFS(Sheet1!$A$3:$A$24,Sheet2!A4,Sheet1!$T$3:$T$24,">0")+COUNTIFS(Sheet1!$A$3:$A$24,Sheet2!A4,Sheet1!$Y$3:$Y$24,">0"),"")

  24. #24
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,000

    Re: SUMIFS/AVERAGEIFS across multiple columns

    @Keith740

    What about my solution in #4.

    You add a file, but don't add the result manualy.

    In that case forummembers can't check their answers.

    So please add the expected result in your file.

  25. #25
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218
    I know you are right oldere.. pivot table would be the best option. Unfortunately i am not familiar with pivotables yet, but learning. Ty for help!

  26. #26
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Re your message #23

    This is probably what you are wanting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  27. #27
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    yes, ty newdoverman!

    im doing something wrong when i apply it to my formula though as it sais " The formula you typed contains errors"

    Any way you could go over my formula and find the issue? I know its something minor im missing, just not used to working with formula of this size...

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  28. #28
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS/AVERAGEIFS across multiple columns

    I think I found the error. It was the misplacement of a ) near the end of the formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  29. #29
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS/AVERAGEIFS across multiple columns

    I don't know what you are calculating because the worksheets are not in your example but this form of the formula may work and be easier to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  30. #30
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    2010 pro
    Posts
    218

    Re: SUMIFS/AVERAGEIFS across multiple columns

    YES!!! TY Sir!! mission complete!

    And thank everyone who has helped me with this issue...

    You guys are great

  31. #31
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMIFS/AVERAGEIFS across multiple columns

    Thank you for the feedback.

+ 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] Sumifs or Averageifs
    By ntate in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-05-2014, 07:45 PM
  2. [SOLVED] Complex if Formulas AVERAGEIFS, SUMIFS, COUNTIFS
    By fredlo2010 in forum Excel General
    Replies: 5
    Last Post: 06-19-2014, 03:26 PM
  3. [SOLVED] AverageIFs / SumIfs
    By Decar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2013, 02:59 PM
  4. Index with sumifs and averageifs
    By LUNARCEA in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2013, 11:59 AM
  5. AverageIfs for multiple criteria in different columns
    By HBEE in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-05-2013, 12:21 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