vlookup or other function

  • Thread starter Thread starter DG
  • Start date Start date
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
 
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.
 
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
 
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)
 
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rearrange sheet data 3
Formula request 2
Job for VLOOKUP? 2
Couting rows based on given data range 1
Sorting 2
Top Ten List by Individual Volume 2
Rank If? 3
What is the best option vlookup is killing me 1

Back
Top