Question on adding and averaging data based on partial values in a field - SumIf? SumProduct?

L

Legacy 170226

Guest
Hi all -

Excel 2010, windows 7

I have a question that I think will involve SumIf, AverageIf and/or SumProduct, but I'm not quite sure.

I have series of import data noting different products being traded
Code: 
, the source of the data [SOURCE], and the time period (here just noted by month).  Sample data below.

For the most part data is all in the same format:  Months all note tons; Source all note the name of the source.

Code is problematic, because the data are in different format within the field.  These data are HS-codes - commodities are divided into two-digit "chapters", and including more digits beyond that provides increased specificity on the product in question (see http://www.usitc.gov/publications/docs/tata/hts/bychapter/1300C15.pdf for further detail on these codes).  For the most part they are 6-digit figures.  Some are 4 digit and some are 5 digit.  

Based on a code referenced in another cell, I would like to do the following operations in the following order[INDENT]
1) summarize all entries by source that have codes that share the same initial four figures from left to right* (e.g. where the text in the cell begins with "1507")
[/INDENT]
[INDENT]2) Average those sums (for each 4-digit CODE) across all sources for a total average for the period

*note the CODE value being searched for in the equation would be referenced from another cell
[/INDENT]

so for cell C30, which looks to perform the operation for all entries where the 4 left-most figures in CODE begin with "1507", the result would equal =Average((C2+C3),(C8+C9),(C19),(C27),(C28), but it would do so without doing hard references to those cells.  In the example table below, I would like for the formula in C30 to reference B30 to find the code that it is looking for.

A few things of note:  

[LIST]
[*]I would like to do this operation without adding another column (=left(Bx,4)) to pull the first four digits from the entry in column B, which I know would make for a simpler formula. 
[*]Sometimes the values in CODE begin with a zero, and must be preceded with an apostrophe to preserve the initial zero (e.g.: '071022).  thus entries in CODE are sometimes recorded as text and not as numbers.  i would like for my formula to take this into account. 
[*]Ideally, I'd like to know how to do this using an array (CSE) forumula, as well as one that does not do it use CSE.  But I'd be happy with either  =). 
[/LIST]
 
thanks in advance for any assistance - n


 
[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="class: xl100, width: 64"]B[/TD]
[TD="class: xl101, width: 64"]C[/TD]
[TD="class: xl101, width: 64"]D[/TD]
[TD="class: xl101, width: 64"]E[/TD]
[TD="class: xl101, width: 64"]F[/TD]
[TD="class: xl101, width: 64"]G[/TD]
[TD="class: xl101, width: 64"]H[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="width: 64"]Source[/TD]
[TD="class: xl100, width: 64"]Code[/TD]
[TD="class: xl101, width: 64"]Jan[/TD]
[TD="class: xl101, width: 64"]Feb[/TD]
[TD="class: xl101, width: 64"]Mar[/TD]
[TD="class: xl101, width: 64"]April[/TD]
[TD="class: xl101, width: 64"]May[/TD]
[TD="class: xl101, width: 64"]June[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150710[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]8[/TD]
[TD="class: xl103, align: right"]95[/TD]
[TD="class: xl103, align: right"]922[/TD]
[TD="class: xl103, align: right"]1174[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150790[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]25[/TD]
[TD="class: xl103, align: right"]28[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150810[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]22[/TD]
[TD="class: xl103, align: right"]33[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150890[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]1[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]151110[/TD]
[TD="class: xl103, align: right"]10278[/TD]
[TD="class: xl103, align: right"]3436[/TD]
[TD="class: xl103, align: right"]8317[/TD]
[TD="class: xl103, align: right"]15901[/TD]
[TD="class: xl103, align: right"]1066[/TD]
[TD="class: xl103, align: right"]192[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]151190[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]2910[/TD]
[TD="class: xl103, align: right"]4506[/TD]
[TD="class: xl103, align: right"]17339[/TD]
[TD="class: xl103, align: right"]3069[/TD]
[TD="class: xl103, align: right"]42494[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150710[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]96[/TD]
[TD="class: xl103, align: right"]922[/TD]
[TD="class: xl103, align: right"]1174[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150790[/TD]
[TD="class: xl103, align: right"]1385[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]26[/TD]
[TD="class: xl103, align: right"]29[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150810[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]22[/TD]
[TD="class: xl103, align: right"]16[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150890[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]151110[/TD]
[TD="class: xl103, align: right"]10278[/TD]
[TD="class: xl103, align: right"]3437[/TD]
[TD="class: xl103, align: right"]3766[/TD]
[TD="class: xl103, align: right"]15901[/TD]
[TD="class: xl103, align: right"]1066[/TD]
[TD="class: xl103, align: right"]15459[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]151190[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]2909[/TD]
[TD="class: xl103, align: right"]4506[/TD]
[TD="class: xl103, align: right"]17338[/TD]
[TD="class: xl103, align: right"]3069[/TD]
[TD="class: xl103, align: right"]42611[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]151590[/TD]
[TD="class: xl103, align: right"]707[/TD]
[TD="class: xl103, align: right"]3[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]151229[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]150890[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]151519[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]151590[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]150790[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1508[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1511[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]151211[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]151212[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1514[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]15131[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]15132[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1507[/TD]
[TD="class: xl103, align: right"]32[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Scott[/TD]
[TD="class: xl102, align: right"]15079[/TD]
[TD="class: xl103, align: right"]890[/TD]
[TD="class: xl103, align: right"]65[/TD]
[TD="class: xl103, align: right"]456[/TD]
[TD="class: xl103, align: right"]500[/TD]
[TD="class: xl103, align: right"]41[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD="class: xl102, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1507[/TD]
[TD="class: xl103, align: right"]X?
(see formula above table)[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1508[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1509[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1510[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1511[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1512[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1513[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1514[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1515[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Andrew Poulsom said:
An array formula:

=AVERAGE(IF(LEFT(B$2:B$28,4)=LEFT(B30,4),C$2:H$28))
Click to expand...


Thanks for the quick response. I should have clarified, I only want the formula to sum and average the values within the column in question (I'll use the same formula for each other month for each CODE value). Editing the formula to do that gives me the equivalent of

=AVERAGE(C2,C3,C8,C19,C27,C28,C9) = 329.57, not
=AVERAGE(SUM(C2:C3),SUM(C8:C9),C19,C27,C28) = 461.4, which is the value that it should add up to.

is there a way to incorporate the SUM and then AVERAGE aspect of that equation into the array?

thanks - n
 
Upvote 0
Thanks Andrew - that's correct.
=AVERAGE(IF(LEFT(B$2:B$28,4)=LEFT(B30,4),C$2:C$28)) provides the same result as
=AVERAGE(C2,C3,C8,C19,C27,C28,C9). both equal 329.57

the response I am looking to elicit is =AVERAGE(SUM(C2:C3),SUM(C8:C9),C19,C27,C28) = 461.4
only doing it in a manner similar to your suggestion, via an array formula or similar.

The array formula matches trade values with a given code, and then averages the values that meed the criteria (=left(B30,4)) without first looking to SOURCE. I would like for it to

1) Generate subtotals
a) for each SOURCE value (in column A)
​
b) for all CODES (Column B) that meet the criteria referenced in the formula (here, the 4-digit CODE in B30)
​
c) in the month in question (data in column C in the example)
​
2) then average together those subtotals generated in #2.
​

This would generate 461.4 in the sample data I provided.


The current formula doesn't reference SOURCE (column A). Can that be added to produce the result I'm looking for?

thanks - n
 
Upvote 0
Sorry, I misunderstood. You want the average of each person's total. Sorry I can't think of a way to do that at the moment.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,375
Messages
6,124,592
Members
449,174
Latest member
chandan4057

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top