+ Reply to Thread
Results 1 to 2 of 2

Using AVERAGE with conditions in different worksheets?

  1. #1
    mommy2kh
    Guest

    Using AVERAGE with conditions in different worksheets?

    I have a workbook where in sheet 1 there is a list of items (such as apple,
    pear, orange). In sheet 2 these items are listed with various prices. How
    can I set up a formula that will take each item in the list in sheet 1 and
    find all the matches in sheet 2 and then determine the average price
    associated with it? I am looking for a forumla I can put in sheet 1 and then
    copy for the entire list so I won't have to retype the forumla each time.
    Your help is greatly appreciated! Thanks.

    Sheet 1
    apple
    orange
    pear

    Sheet 2
    apple $1
    apple $2
    pear $3
    pear $4
    pear $3
    orange $6
    orange $7
    orange $3

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    If your sheet1 criteria (apple, orange, pear) are in A1 down and list of fruits is in sheet2 column A with dollar amounts in sheet2 column B then use this formula in sheet1 B1 copied down

    =SUMIF(Sheet2!A:A,A1,Sheet2!B:B)/MAX(1,COUNTIF(Sheet2!A:A,A1))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1