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!!
Hello
Maybe the formulas (green cells) in the attached reply will do as you require?
DBY
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...
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.
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
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...
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)
Agent Total Hours Total % Hours Hours Hours Bill R Chris F Eric B Erin F Jarod B Joseph D Kent D Kevin D Mike C Mitch N Paul W Stephen C Steven M William G
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
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.
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?
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
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)
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)
Enter in B3 and fill down:
Formula:
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:Please Login or Register to view this content.
Enter in C3 and fill down: This is the average of the averages
Formula:Please Login or Register to view this content.
Enter in H3 and fill down:
Formula:Please Login or Register to view this content.
Enter in I3 and fill down:
Formula: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
yes for each name (will be colomn C)
and then for each code (will be column I)
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.
This formula will average the percentages disregarding the blank cells in the Percent columns:
Formula: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.
What answer are you expecting as the average for, say, jarod B, for the average, and how do you arrive at that?
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
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%
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
@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:Please Login or Register to view this content.
Formula for column I:
Formula: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%
This is for jarob b...
Total Count Average
It is still not accurate though, averages of averages are not correct
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"),"")
@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.
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!
Re your message #23
This is probably what you are wanting:
Formula:Please Login or Register to view this content.
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:Please Login or Register to view this content.
I think I found the error. It was the misplacement of a ) near the end of the formula.
Formula:Please Login or Register to view this content.
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:Please Login or Register to view this content.
YES!!! TY Sir!! mission complete!
And thank everyone who has helped me with this issue...
You guys are great
Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks