+ Reply to Thread
Results 1 to 5 of 5

Count number of rows that match criteria and apply values relative to another criteria

  1. #1
    Registered User
    Join Date
    03-19-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    21

    Count number of rows that match criteria and apply values relative to another criteria

    Hi,

    I maintain a product list spreadsheet and would like to create a formula to apply a per unit price to determine how much my cost is by category. The per unit price (col C) is relative to the supplier (col B) and is discounted by volume – the more I buy the cheaper it is etc.

    The purpose of the spreadsheet is to identify how much my spend is on either beef or chicken. As a category, beef and chicken is then made up of different products, Angus, scotch, sirloin etc.

    What I want to do is apply the per unit price by row relative to the count of “Beef” (or chicken) and by supplier, reflecting the tiered pricing as the count increases.

    I have attached a spreadsheet example with a table of the outcome I am trying to achieve using formulas.

    Data is illustrative; haven’t given an example for supplier 2; data assumes the same buy price for "beef" and "chicken"

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,498

    Re: Count number of rows that match criteria and apply values relative to another criteria

    The word Angus occurs 93 times in column A, 72 for supplier 1 and 21 for Supplier 2. There is no way do discriminate between them. The same for the rest of column A entries.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    03-19-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Count number of rows that match criteria and apply values relative to another criteria

    Quote Originally Posted by protonLeah View Post
    The word Angus occurs 93 times in column A, 72 for supplier 1 and 21 for Supplier 2. There is no way do discriminate between them. The same for the rest of column A entries.

    Thanks for your quick response. In my mind I was trying to use an IF Statement which, if the value was "supplier 1" in Col B, "count the number of rows angus, scotch and sirloin appeared, then based on another statement apply the supplier 1 pricing, based on the graduated pricing etc.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,498

    Re: Count number of rows that match criteria and apply values relative to another criteria

    1. Defined names:
    Please Login or Register  to view this content.
    2. Note blue look up helper columns
    3. Dropdown list in G13 to select supplier

    4. Formula to determine product category (O14 - O18)
    Please Login or Register  to view this content.
    5. Formula to calculate price/cost (I16, etc.)
    Please Login or Register  to view this content.
    6. Count of product per supplier:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 10-31-2016 at 12:49 AM.

  5. #5
    Registered User
    Join Date
    03-19-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Count number of rows that match criteria and apply values relative to another criteria

    Hi protonLeah.

    That is awesome, very impressive. Many thanks!

    Puni

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How do you count the number of dates that match a criteria
    By john dalton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-01-2014, 09:23 AM
  2. [SOLVED] Count the number of instances where multiple criteria match
    By kurt.l in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-10-2012, 12:12 PM
  3. Replies: 1
    Last Post: 07-27-2012, 02:37 PM
  4. Replies: 3
    Last Post: 08-09-2010, 09:07 AM
  5. Replies: 5
    Last Post: 09-22-2009, 06:11 PM
  6. Count rows that match 3 sets of criteria?
    By EricE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2005, 12:30 PM
  7. How to count rows that match two criteria?
    By Broida (spamless) in forum Excel General
    Replies: 7
    Last Post: 01-02-2005, 06:09 PM

Tags for this Thread

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