Formula to count number of products in Time Interval

[bjnockle]

Have three headings: products, start time and end time (in column A, B and C) of sheet named Products. Looking for formula to count products (in range A2:A59) if the products exist within that time interval using.

Values in range A2:A59 are unique if it is seperated by only one comma (for example, Apple, Aglianico is a unique product. See column I for how this should be setup with a formula).

Formula for product counts should be in sheet named Outcome. Two design tables setup. One is horizontal and the second one is vertical. Looking for formula for both cases.

See attached sample file.

[Glenn Kennedy]

There is a problem with your data. Your rule concerning commas is not consistently applied.

Row 12: Blackcurrant,vegan, has two commas but is one fruit.
Row 13: Plum, Jr., has two commas but is one fruit
Row20/28/29: Same fruit different coma pattern.

Are your real data as inconsistent as that? If not can you correct and repost? Also, please make it clear which columns require a formula in the PRODUCTS sheet.

[bjnockle]

Glenn: Fixed data error on Row 12, row 13 and row 20/28/29.
Column F, G, H and I will require formula in the PRODUCTS sheet.
Row B2:T2 will require formula in the Outcome Sheet
A6:A37 will require formula in the Outcome Sheet

Sample file re-posted.

Thanks

[bjnockle]

Glenn: Fixed data error on Row 12, row 13 and row 20/28/29.
Column F, G, H and I will require formula in the PRODUCTS sheet.
Row B2:T2 will require formula in the Outcome Sheet
A6:A37 will require formula in the Outcome Sheet

Sample file re-posted.

Thanks

[Glenn Kennedy]

Still not sure how to do this. My initial thought was if there's only one comma, there's only one fruit. If there are 2 (or 3) commas, there are two (or 3) fruits, and separate the fruits from each other at the position of the 2nd (and 3rd) commas. However, as you can see, even in the corrected sheet, on every occasion where there are multiple fruits: the number/position of commas is inconsistent. No pattern = no ready solution. I can't use two words = 1 fruit, 4 words=2, 6=3, because of Plum Jr.,Weight (3 words... & 1 fruit)

[Glenn Kennedy]

Hang on though... I can use the number of words (...maybe). For example, this will work for column I.

=LOOKUP(INT((LEN(A2)-LEN(SUBSTITUTE(A2,",","")))/2)+1,{1,2,3},{"One","Two","Three"})&" Fruit"

[Glenn Kennedy]

No. It's messy.

[bjnockle]

Gleen: Example: Gooseberry, Bosco, Currant, Arneis

Gooseberry, Bosco is a unique fruit and it is one fruit.
Currant, Arneis is a unique fruit and it is the second fruit
the comma (comma is bolded) separating them (Gooseberry, Bosco, Currant, Arneis) is to to let you know they are two separate fruits, which will give you two fruits.

It is like fruit having first and last name seperated by commas. For example, Oliver, Twist and Kennedy, Gleen. Joining them will give, Oliver, Twist, Kennedy Gleen. This is equal two names, which is the same as two fruits.

Thanks

[Glenn Kennedy]

Sometimes... I can be so dense, it's not true... Take a look at Products. make sure it's OK & let me know. I'll be back and forward for a few hours and will look at the other bit asap.

[bjnockle]

Glenn: reviewed the file. Great solution. Exactly what I am looking for. Thanks

[FlameRetired]

Edit Please disregard. Messes up on the Jr. and Sr. when multiple fruits.

Another way.

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,", ",REPT(" ",256)),(COLUMNS($F:F)-1)*512+1,512))," ",", ")

[FlameRetired]

This fixes the Jr. / Sr. part.

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,", ",REPT(" ",256)),(COLUMNS($F:F)-1)*512+1,512))," ",", ",ISNUMBER(FIND({"Jr.","Sr."},$A2))+1)

[bjnockle]

Great solution FlameRetired. Looking for formula for the time interval piece on the Outcome sheet.

[Glenn Kennedy]

Ooops. i forggot about you!! Try this (slight modifications made...)

[FlameRetired]

................... Looking for formula for the time interval piece on the Outcome sheet.

Looks like Glenn covered it.

[bjnockle]

Glenn: Product count is not counting correctly on the Outcome sheet. See correct product count outcome in C6:C25. Sample formula is shown below. Sample formula is not
working as desired. Will need to be tweaked to achieve result.

Formula write up should look into range A2:A25 and range C2:C25 of Products sheet for the product count per hour.

Sample formula: not working.

Please Login or Register  to view this content.

On Products sheet, added product IV in J2:J25. Need to expand the formula to cover 4 scenarios of products. This is just an extension of this working formula

Please Login or Register  to view this content.

See sample file.

Thanks

[Glenn Kennedy]

1. Is Blackcurrant, Vegan, Alicante, Bouschet one product or two? I had assumed that it is 2 because of the formulae on the first sheet that are separating them into two separate product columns.

2. Why is your expected answer for 07:00 to 08:00 equal to 8? There are 19 lines... all of them are for sale during that period. Those 19 lines consist of a total of 22 individual fruits.

So, please explain eaxctly what your spec is. I have added on the 4th fruit...

[bjnockle]

Glenn, Blackcurrant, Vegan, Alicante, Bouschet is two products. Blackcurrant, Vegan is one product and Alicante, Bouschet is another product. For the count, would like it to only count products using the start time. Thanks

[Haseeb Avarakkan]

Hello,

If you have pair of fruits in A2:A59 separated by comma try this

=SUMPRODUCT((Products!B$2:B$59>=A6)+0,(LEN(Products!A$2:A$59)-LEN(SUBSTITUTE(Products!A$2:A$59,",",""))+1)/2)

[Glenn Kennedy]

=SUMPRODUCT(--(A6<=Products!$C$2:$C$59)*--(A7>Products!$C$2:$C$59)*--Products!$K$2:$K$59)

in B6, copied down. however, your expected answers are incorrect, given that 2 fruits = 2 fruits!!

[bjnockle]

Glenn: Products!$K$2:$K$59 should be looking into range A2:A59 for the expected results. Should be Products!$A$2:$A$59. Adjusted formula to =SUMPRODUCT(--(A6<=Products!$D$2:$D$59)*--(A7>Products!$C$2:$C$59)*--Products!$A$2:$A$59) and it is return an error message. Please check. No longer counting by No of fruits. Counting should be based on Products Origin in column A of Products sheet.

Thanks

[bjnockle]

Haseeb: formula not displaying the correct results. What is in column A2:A59 is not fruits but product origin (like state/region). There will never be comma in this range. Just single value.

Thanks