Hi,
Please help me with this. This is really urgent that i need to learn this. I really don't know how to use vlookup and sum/average function together.
I already tried AVERAGE(VLOOKUP(A2,data!A$2:C$13,3,FALSE)), but it only returns the first value in the data tab.
I was thinking that vlookup data should be placed in an array.. but don't know how... =(
Pivot table is placed there only as reference as to see if calculations will be correct.
Can someone please educate me on this.. I would really appreciate this. Thank you.
Use these:
=SUMIF(data!A$2:A$3,A2,data!C$2:C$13)
and
=AVERAGEIF(data!A$2:A$3,A2,data!C$2:C$13)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
that works. Thanks. =)
but is there any other methods, the assignment was to use the vlookup to get the sum/average.. given that if in the data table, joe1 was entered. . it would still be able to get the sum/average.. ..
If there are going to be duplicated matches (i.e. name appears twice in same month) then vlookup won't work.... If there is only one appearance of the name per month, then you don't need to sum or average at all... so I am not sure what the issue is then.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
i mean, if we were to put a new data since this would be a running data. and the data inputed is (row 14: joe1, march 2011, 230).
Is there a way where we could probably catch it in the calculation of the sum/average of joe.
I apologize if Im not that clear. . .
Use dynamic named ranges then: http://www.contextures.com/xlnames01.html#Dynamic
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Or,
You can use whole column reference.
B2, copy down.
=SUMIF(data!A:A,A2,data!C:C)
C2, copy down.
=IFERROR(AVERAGEIF(data!A:A,A2,data!C:C),"")
B11, copy down.
=SUMIF(data!B:B,">="&A11,data!C:C)-SUMIF(data!B:B,">"&EOMONTH(A11,0),data!C:C)
C11, copy down.
=IFERROR(AVERAGEIFS(data!C:C,data!B:B,">="&A11,data!B:B,"<="&EOMONTH(A11,0)),"")
A11 to down, should be first day of the month.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
The point was the OP was asking to sum or average results using VLOOKUP...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
still can't get it. . . Is there a way to capture all vlookup caputured data in an array?
=VLOOKUP(A2,data!A$2:C$13,3,True) will read the name even if joe1 is entered, but if you where to arrange it in ascending order it only captures the closest value not the exact.
No, Vlookup responds with the first instance of the array that matches the criteria.
If the endgame of the equation is to obtain the average or sum or all situations where the conditions occur... then you'll need to go with the averageif or sumif equations. I believe you may be able to use wildcards in the Averageif or Sumif equations to reflect the variations on the fuzzy search between joe and joe1 which you would get with the "True" function at the end of the Vlookup.
I '<3' reputation. If I helped, click the scales. This will be helping an internal departmental competition with co-workers.
Reputation can be granted through the Scales Icon (for classic layout), or the little Star on the bottom of the post next to the blogging function for the new forum layout.
If you're not busy, and really feel down on life, read my excel blog which may or may not have contents.
ok. Thanks for the help guys. That means I have to go on and use wildcards...
=SUMIF(name,A2&"*",prod)
=IFERROR(AVERAGEIF(data!A:A,A2&"*",data!C:C),"")
I did thought that lookup could be used to actualy be used to error check or something. .
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks