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

**Puni** · 10-25-2016, 04:29 PM

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

**protonLeah** · 10-25-2016, 06:21 PM

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.

**Puni** · 10-25-2016, 09:09 PM

Originally Posted by protonLeah

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.

**protonLeah** · 10-30-2016, 10:36 PM

1. Defined names:

Category           =Sheet1!$O$4:$P$7
Multipliers        =Sheet1!$I$4:$J$7
ProductList        =Sheet1!$A$2:$A$356
Supplier           =Sheet1!$G$13
SupplierDiscounts  =Sheet1!$G$4:$H$7
SupplierList       =Sheet1!$B$2:$B$356
Tiers              =Sheet1!$F$4:$F$7

2. Note blue look up helper columns
3. Dropdown list in G13 to select supplier

4. Formula to determine product category (O14 - O18)

=MAX(IF(--(Category=F14),COLUMN($K$1:$L$1)-COLUMN($K$1)+1))

5. Formula to calculate price/cost (I16, etc.)

=INDEX(SupplierDiscounts,MATCH(I$13,Tiers,0),MATCH($G$13,{"Supplier 1","Supplier 2"},0))*INDEX(Multipliers,MATCH(I$13,Tiers,0),$O16)

6. Count of product per supplier:

=COUNTIFS(ProductList,F14,SupplierList,Supplier)

**Puni** · 11-01-2016, 03:28 PM

Hi protonLeah.

That is awesome, very impressive. Many thanks!

Puni

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How do you count the number of dates that match a criteria

[SOLVED] Count the number of instances where multiple criteria match

find greatest value less than criteria (lookup/match?), use relative cell values

count numbre of rows in a column that match a certain criteria

Compare two cells in a row with Criteria. Count Rows that match Criteria

Count rows that match 3 sets of criteria?

[SOLVED] How to count rows that match two criteria?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions