vlookup or other function

D

DG

I have two sheets with data on them.

Sheet two contains item numbers and monthly usage:

Item Usage Month
ABC 8 1
ABC 0 2
ABC 2 3
DEF 5 1
DEF 2 2
DEF 1 3

Sheet 1 contains the item numbers and I want to add two columns Avg and
Total. Is there a function that I can put on sheet one to total the usage
and another to average the usage from sheet 2?

Dan
 
H

Hittin_Provs

Im not sure if this answer is what you mean but....

=average(c2:c8)

=average(sheet2!c2:c8)

Im not sure if I am understanding your question correctly, but this
will give you the average of monthly usuage for both sheets.
 
D

DG

Not quite.

Sheet 2 has all usage data for each item for the last 12 months. Therefore,
there are 12 rows per item.

Sheet 1 I am trying to consolidate all that so I only have 1 row per item.

I use vlookup often to find data in other sheets but that is always for one
line. I'm looking for something that works like vlookup that will go to
sheet 2 and find all the records with the same item number and average or
total the usage number from the other column.

Example: if this was sheet 2

Item Usage Month
ABC 8 1
ABC 0 2
ABC 2 3
DEF 5 1
DEF 2 2
DEF 1 3

Then Sheet 1 would calculate the Average for ABC items to be 10/3 = 3.3 and
the total for ABC would be 10 (8+0+2) as shown here:

Item Avg Usage Total
ABC 3.3 10
DEF 2.6 8

Is that better? I probably could use average and sum but the number of
months is not always 12. And there are over 5000 items so I want to
automate as much as possible.

DG
 
R

RagDyer

Try this in B2 of Sheet1 for the average:

=SUMIF(Sheet2!A$2:A$10,A2,Sheet2!B$2:B$10)/COUNTIF(Sheet2!A$2:A$10,A2)

And try this in C2 for the total:

=SUMIF(Sheet2!A$2:A$10,A2,Sheet2!B$2:B$10)
 
D

DG

Thanks, I think that's got it.

DG

RagDyer said:
Try this in B2 of Sheet1 for the average:

=SUMIF(Sheet2!A$2:A$10,A2,Sheet2!B$2:B$10)/COUNTIF(Sheet2!A$2:A$10,A2)

And try this in C2 for the total:

=SUMIF(Sheet2!A$2:A$10,A2,Sheet2!B$2:B$10)
Click to expand...
 
Top