Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need SUMIF / SUMPRODUCT help I think
After searching for hours I have resulted to posting a question. Sorry if
this is a duplicate but couldn't find what I needed. I have a very large range of data I need to pull apart to get a breakdown of totals from. I am pulling this to a different page and then converting to values so I can delete the data. I do this every day to track daily sales. (I will say I am not familiar with macros at all.) (Data example below) Company A Company B Company C through Col GI Col A Col B Col C Col D Col E Col F Item Value Item Value Item Value 1E $2 1W $5 3E $3 5W $2 3E $5 5E $3 3E $2 6W $5 4W $3 1E $2 2W $5 5E $3 through row 3000 I have a range name set as "REV_DATA" to cover A4:GI3000 already. So each letter number combination represents an item sold for the company in the column its under. On my other tab/worksheet I am totaling how much revenue was brought in for that item sold across all companies. (This is also stored as how much was sold for a specific company so I can't do away with my above structure.) From the example above: 1E $4 1W $5 3E $10 The only formula I can come up with would be for each item (900 individual items). In a perfect world a formula that would use my range (REV_DATA) to pull the individual items sold for how much would be great. Any help would be greatly appreciated and would save me HOURS of work. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need SUMIF / SUMPRODUCT help I think
This formula is sized for your example (A3 to F6)
Say you have a list of items starting in H1 down. enter this in G1, and copy down as needed: =SUMPRODUCT(--(A$3:E$6=H1),B$3:F$6) Note that you go from columns A to E, then from B to F! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Big_Tater" wrote in message ... After searching for hours I have resulted to posting a question. Sorry if this is a duplicate but couldn't find what I needed. I have a very large range of data I need to pull apart to get a breakdown of totals from. I am pulling this to a different page and then converting to values so I can delete the data. I do this every day to track daily sales. (I will say I am not familiar with macros at all.) (Data example below) Company A Company B Company C through Col GI Col A Col B Col C Col D Col E Col F Item Value Item Value Item Value 1E $2 1W $5 3E $3 5W $2 3E $5 5E $3 3E $2 6W $5 4W $3 1E $2 2W $5 5E $3 through row 3000 I have a range name set as "REV_DATA" to cover A4:GI3000 already. So each letter number combination represents an item sold for the company in the column its under. On my other tab/worksheet I am totaling how much revenue was brought in for that item sold across all companies. (This is also stored as how much was sold for a specific company so I can't do away with my above structure.) From the example above: 1E $4 1W $5 3E $10 The only formula I can come up with would be for each item (900 individual items). In a perfect world a formula that would use my range (REV_DATA) to pull the individual items sold for how much would be great. Any help would be greatly appreciated and would save me HOURS of work. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMIF vs SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumif or sumproduct | Excel Discussion (Misc queries) | |||
SUMIF/SUMPRODUCT?? | Excel Worksheet Functions | |||
Sumif not Sumproduct | Excel Worksheet Functions |