|
|
Dear all, allowed me to use English to explain my confusions. I am working overseas, cannot type CHS by Company Computer.
I have been using Excel for my job only 3 month, there has already no people in my company can help me with this software. However, it is clear that I am only a fresh in the field. Therefore, please kindly help me without big laugh.
Okay, we are going to the point now. My confusion is :1.Vlookup is one of the most uesful funtion. However, it only searches the target from top to the button, and only picks up one number, so does Index/Match. Therefore, I was trying to use the Sumifs to summarize the results, in the cases of having duplicate targets. However, I found the sumif works does not accurate as Vlookup or index.
Sumif (<range>, <condition>, <results>), if the range array over the condition array, the result will be wrong. For example, I have 16 outlets; each of them has breakfast, lunch, dinner. All the outlets names are stored in column A, but according to the order of 1.covers, 2.food revenue, 3.beverage revenue and 4 other revenue. All the Revenue figures will be stored in the rest columns by months. So the sheets original Sheet will display like this:
Column A Column B Column C Column D …… Column X
Cover Jan Feb Mar…… Total
Outlet1/breakfast 1111 2222 3333 =sum(B:x-1)
Outlet1/lunch …. …. ….. ….
….
Outlet16/dinner
Food Revenue
Outlet1/lunch
Outlet1/dinner
….
Outlet16/dinner
Beverage Re
….
other Revenue
…..
Grand total
If I want to know how much is the Outlet 1’s total by using sumif, I have to choose the searching <range> against the <result> array. I mean one by one. If I choose the Column A as <range>array and choose some part of cells in Column X <result>array, say only cover part. The actually result will not return the only cover figures. Instead of that, it will return the correct figure by 4 times. I suppose that because the sumif not actually function like index or vlookup. It actually works like “pick up the results according to the order of which matchs the targets.” Therefore, in whole Column A, the Outlet 1’s name repeats for 12 times, so the results which returns 12/3=4 times. This happened when the 4 part layouts are all same, if the layout of this table is changed; the result will be very colourful.
Therefore, here comes the question. Is there any possiblity that summarize multiple targets in one formula? Which means can combaine both Sumif and Vlookup's advantage. Please give me the detail.
PS: Apologize again with English writting, I am in a hurry, therefore, if the writting confuse you, then I am terribly sorry. FYI, Our job is confidencental, so allow me can not uploading the real files,
Thanks and Best Regards |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-11-11 22:34
