3 criteria SUM

Hi

I am trying to solve a problem related to 3 criteria of selling food item. Please see attached file to get my problem and what I am looking for. for example i provided for two days data. I have to do this for seven days. Pls let me know if u hvae any question.

Thanks

very unclear

top1, please dont quote whole posts. In fact, only quote if you're not referring to the last post, and then only quote the pertinent bits.

Your data is very hard to use for summing up. For each cell you'd have to
- search for a specific text string
- search for the blank after the number
- extract the number

Then you' d have to do that for all text strings of the same type in many columns in order to add up totals per food type and size.

That will create a very unwieldy formula. It would be much easier to create a solution if your data can be arranged in a tabular form, with each field in a column and each record in a row, i.e.

Date - food type - size - amount
8-Feb - pepperoni - 6 - 22
8-Feb - pepperoni - F - 79
8-Feb - cheese - 6 - 17

etc.

It would also help if the food types were spelled consistently.

Then you can use a pivot table to create totals per date, food type, size, whatever.

I must have to use this data format bc i m getting it the way I shown. I have to do this for months. Again for clarification in data just example

Chichen (6:3 F:2) this data shows six inch of 3 chicken sandwich and 2 foot longs.
Chicken( (6:4 F:2) which shows 4 six inches and 2 footlongs .. this is my data. I want to sort them in this way

Chicken
6 F
7 4 <<<<this numers are sum of each size.

top1,

This is a duplicate post and as such does not comply with Rule 5 of our forum rules.

Given your earlier thread was as yet unanswered that will be closed.


If a day has passed and you have yet to receive a response you are more than entitled to "bump" the post back up by replying to your own thread - do not post duplicates.

If you haven't had a response it's more than likely down to your explanation - so think about restating your requirements as clearly as possible

I must have to use this data format bc i m getting it the way I shown.

It may be a lot easier to write a macro to splice out the data into a table than to try and construct a formula that does the summing up for dozens and dozens of cells where each cell needs to be spliced up indiviually.

I confess I did look at this yesterday however the original sample file had erroneous values which didn't help and incorrect expected results based on the data.

I would say that unless you know a given quantity will always be between 0-99 you will find it quite difficult to extract using native functions.

If you assume the 0-99 holds true then in theory (once you've corrected for typo in A4 in your sample) the below:

Please Login or Register  to view this content.

which can be applied across matrix B9:D12 and would generate:

Please Login or Register  to view this content.

If you can't make the above assumptions then I agree that a UDF is the most logical step... utilising Regular Expressions perhaps

It's a curse...

No, in all seriousness I wouldn't necessarily advocate the approach illustrated as it is

a) not efficient - will be noticeable over an ever increasing data set

b) not very robust - will fail should any of the values post colon be >=100
(we assume decimals and negative values aren't an issue in this instance)

c) not very flexible - should the layout of your strings change the formula is unlikely to work

d) not very easy to understand / audit and/or fix as and when

e) I'm sure it could be improved upon

In short the point made by Teylyn holds true (always) - how you store your source values is crucial for subsequent analysis.

Originally Posted by top1

I must have to use this data format bc i m getting it the way I shown.

The above may indeed hold true however I would still be inclined to concentrate on using VBA to reorganise the source values in a meaningful way such that I ended up with a nice contiguous tabular dataset.
With a transaction table the techniques applied to analyse it will be:

a) efficient

b) robust

c) flexible

d) simple

e) optimised

(ie all those things the present solution is not)

On an aside if you consider the thread closed please mark as such.

Thanks,
D.O.

Hi DonekyOte,

Is there any possbile way to modify ur formula for quantities between 99-200???

Originally Posted by top1

Is there any possbile way to modify ur formula for quantities between 99-200???

You can embed additional Substitute's into the function but as you can see it will become increasingly inflexible... (changes in red)

Please Login or Register  to view this content.

there are undoubtedly preferable alternatives but the above is that which springs to mind at first glance.

Thanks DonkeyOte ur suggestion worked. Thanks again. I am trying to understand the suggestion. I didnt get the red portions. If u can explain me

SEARCH(B$8&":",$A$2:$C$5&"6:000F:000B:000"),5),B$8&":",""),")",""),"F",""),"B","")))

The function simply extracts 5 characters from each string where first character is determined by position of either 6: / F: / B: depending on the type being searched for.

Based on your sample string structure:

alphastring (6:# F:# B:#)
where # is variable between 1-3 digits in length and not all types need be contained in the string (ie 6: may or may not appear, same for F, B)

It follows that for a 5 char string there are a number of possible variations in the resulting string.

Assume you're looking for 6:# ... the possibilities are:

a) single digit followed by space and another non-numeric - eg: 6:#_F; 6:#_B

b) single digit followed by closing parenthesis - eg: 6:#)

c) two digit followed by space - eg: 6:##_

d) two digit followed by closing parenthesis - eg: 6)

e) three digits eg: 6:###

First the type (6: etc) is removed from the string

Please Login or Register  to view this content.

(by adding 6:000F:000B:000 to each string at the end we know we will always find a match for each type [even if not in original] and where used we have a 3 digit value of 0)

then to account for a) to d) above further Substitute functions are run to remove the remaining non-numerics should they exist.

Please Login or Register  to view this content.

The final string is then coerced to number via double unary operator.


Note: in the above the assumption is that 6:# will always be listed first should it exist within any given string.