a formula to go in a macro

[Anthony]

Hi

Have written basic macros before.

Another program produces a report in excel as layed out below

Column B - Animal type ie Bull, Steer, Cow
Column C - Number of animals
Column D- Weight

Sample

Bull 23 11000
Bull 10 5400
Steer 10 3670
Cow 2 760
Steer 2 860

I manually produce a summary by type with numbers and weights totalled.

EAch time I produce new data I need to start again.

What formula do I write to produce a summarry table relating to the above
data which finds the animal types and identifies total number plus weight.
As animal types change each event I need to formula to look and find what
types are there.


Hope this makes sense

 

[FSt1]

hi
without seeing your macro, it would be hard to say how to work it in but i
would suggest you you look into the countif and sumif formulas.

regards
FSt1

 

[Patrick Molloy]

doesn't a pivot table do that for you?

 

[Ron Rosenfeld]

Hi

Have written basic macros before.

Another program produces a report in excel as layed out below

Column B - Animal type ie Bull, Steer, Cow
Column C - Number of animals
Column D- Weight

Sample

Bull 23 11000
Bull 10 5400
Steer 10 3670
Cow 2 760
Steer 2 860

I manually produce a summary by type with numbers and weights totalled.

EAch time I produce new data I need to start again.

What formula do I write to produce a summarry table relating to the above
data which finds the animal types and identifies total number plus weight.
As animal types change each event I need to formula to look and find what
types are there.


Hope this makes sense

If I understand you correctly, you could use a Pivot Table.

If you drag Type to the Row area; and Count and Weight to the Values or Data
area, you can generate a report like:

Type Total Count Total Wt
Bull 33 16400
Cow 2 760
Steer 12 4530

If you also use a dynamic range name to define your data table, and use that as
the source for your pivot table, then refreshing the pivot table will also
adjust the range appropriately.


A dynamic range name might look like:

=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),3)

This format assumes, of course, that there are no blanks in Column B within the
range.

--ron