Sum, Average, and Count from imported .txt file
 

I must import a large text file with one record for each transaction, then
grab all transactions of the same type within a data range. This I can do.

But within this group I also need to then break into sub groups based on
value ranges (1-10,11-20, etc) and then to count the number of transactions
and average the value of all transactions within each subgroup.

I think I need to keep each record in each subgroup somewhere then do the
averaging and then record the count and average for each subgroup on a
report.

I'm new to excel so any help would be appreciated!

Sum, Average, and Count from imported .txt file
 

I think you will need some sort of COUNTIF() or SUMPRODUCT() formula. You
don't provide any details about the layout of your sheet, so generically...

Column A - values you want to filter by

=COUNTIF(A:A,"=1") - COUNTIIF(A:A,"10")
=SUMPRODUCT(--(A1:A1000=1),--(A1:A1000<=10))

Something like that. Does that get you started?

More reading on Sumproduct():
http://www.xldynamic.com/source/xld....T.html#classic

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"sdhi" wrote:

I must import a large text file with one record for each transaction, then
grab all transactions of the same type within a data range. This I can do.

But within this group I also need to then break into sub groups based on
value ranges (1-10,11-20, etc) and then to count the number of transactions
and average the value of all transactions within each subgroup.

I think I need to keep each record in each subgroup somewhere then do the
averaging and then record the count and average for each subgroup on a
report.

I'm new to excel so any help would be appreciated!

Sum, Average, and Count from imported .txt file
 

Hi,

You may create a pivot table. Drag the values column to the row area and
transactions to the data area. Now click on any cell in the row area and go
to pivot table drop down on the pivot table toolbar. Select Group and Show
detail Group. Fill in the required input.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"sdhi" wrote in message
...
I must import a large text file with one record for each transaction, then
grab all transactions of the same type within a data range. This I can do.

But within this group I also need to then break into sub groups based on
value ranges (1-10,11-20, etc) and then to count the number of
transactions
and average the value of all transactions within each subgroup.

I think I need to keep each record in each subgroup somewhere then do the
averaging and then record the count and average for each subgroup on a
report.

I'm new to excel so any help would be appreciated!