Excel Formula: sumif based on semi-colon-separator V2
Hello experts,
I have the following sheet.
I would like to set up sumif formula in order to take as a reference process delimited with ; separator.
I attached dummy file:
Instead of reporting manually the values concerned in the sum I would like to read the values reported as of range A15 delimited by ";" and perform the sum as of column B16.
If you have questions, please contact me.
Sumif-based-on-semi-colon-separator.xlsx
I have the following sheet.
I would like to set up sumif formula in order to take as a reference process delimited with ; separator.
I attached dummy file:
Instead of reporting manually the values concerned in the sum I would like to read the values reported as of range A15 delimited by ";" and perform the sum as of column B16.If you have questions, please contact me.
Sumif-based-on-semi-colon-separator.xlsx
You can use this formula in B15
=SUMPRODUCT((ISERROR(FIND($B$2:$B$7&";",A15&";"))=FALSE)*$C$2:$C$7)ASKER
Thank you Ejgil.
Formula works for values from 1-9 however when I report a value superior than 10 example 11 the value is not properly displayed.
I attached my reference file in which I set up the proposed formula in 2-Key-Actions with screenshot from 1-Specific-Actions.
As you can see when I report value 11 and I got 8.6, instead of having 3.6.
Thank you in advance for your help.
Sum-product-test_20122018.xlsx
Formula works for values from 1-9 however when I report a value superior than 10 example 11 the value is not properly displayed.
I attached my reference file in which I set up the proposed formula in 2-Key-Actions with screenshot from 1-Specific-Actions.
As you can see when I report value 11 and I got 8.6, instead of having 3.6.
Thank you in advance for your help.Sum-product-test_20122018.xlsx
The 8.6 is because its taking the time for process 1 and the time for process 11 (1=5 plus 11=3.6, result 8.6)
Does your process identifier have to be numeric?
Would a change in process to using process identifier A to Z be feasible?
A numeric identifier is currently limiting you to 10 processes (0 - 9) whereas an alphabetic identifier would increase that limit to 26.
Would a change in process to using process identifier A to Z be feasible?
A numeric identifier is currently limiting you to 10 processes (0 - 9) whereas an alphabetic identifier would increase that limit to 26.
ASKER
Ok, thank you for this feedback, in that case I can rename A1,A2... however I was wondering if there is a way to review the formula to be applied for the various numeric values?
See attached with my suggestion for just alphabetic process identifier; this still uses the helper columns as before.
By the way, it would have helped if you had given all of the conditions from the start rather than changing part way in to the question.
Sum-product-test_20122018.xlsx
By the way, it would have helped if you had given all of the conditions from the start rather than changing part way in to the question.
Sum-product-test_20122018.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. Unable to test it now. I will keep you informed.
Tested Ejgil's suggestion with multiple occurrences of the same process in the string in column E and it only counts each process once. For example, if E4 was "11;11" rather than just 11 the result is still 3.6 rather than 7.2 for two occurrences.
See attached.
Column H creates a string which converts all numbers within the string to a string starting with an underscore and ending with a semi-colon. Thus 11 becomes "_11;" (without the quotes). This is then distinguishable from an entry of 1 as that becomes "_1;".
The helper columns (I to AH) then find the specific string for the number in row 1 and replace them with "_;" which reduces the length of the string by removing the number in row 1.
The comparison of original length to reduced length then determines how many occurrences of the particular number have been removed. Where the number in row 1 is more than one digit, the reduction has to be divided by the number of digits as that is how many occurrences of that number have been removed.
The result of the occurrences calculation is then multiplied by the relevant value from the process list.
I am thinking that if the logic applies for 1 or 2 digit numbers, I don't see a reason why it wouldn't work beyond 2 digits.
Sum-product-test_20122018.xlsx
See attached.
Column H creates a string which converts all numbers within the string to a string starting with an underscore and ending with a semi-colon. Thus 11 becomes "_11;" (without the quotes). This is then distinguishable from an entry of 1 as that becomes "_1;".
The helper columns (I to AH) then find the specific string for the number in row 1 and replace them with "_;" which reduces the length of the string by removing the number in row 1.
The comparison of original length to reduced length then determines how many occurrences of the particular number have been removed. Where the number in row 1 is more than one digit, the reduction has to be divided by the number of digits as that is how many occurrences of that number have been removed.
The result of the occurrences calculation is then multiplied by the relevant value from the process list.
I am thinking that if the logic applies for 1 or 2 digit numbers, I don't see a reason why it wouldn't work beyond 2 digits.
Sum-product-test_20122018.xlsx
If you don't like the idea of helper columns but you need multiple occurrences counted then I suspect you will have to use VBA in a User Defined Function which looks at the string in column E and splits it at each occurrence of the semi-colon to form an array of numbers which is then compared to the table for the relevant values against each.
That is beyond my VBA/UDF knowledge but am aware that there are experts on here that would be able to do that.
That is beyond my VBA/UDF knowledge but am aware that there are experts on here that would be able to do that.
ASKER
Thank you very much Rob for your proposals.
ASKER
The number of process cannot be reported twice in the reference colum as a result this will not be a problem.
This will be the last requirement, possible to return "Unable to sum as process reported are not unique".
Thank you again for your help.
This will be the last requirement, possible to return "Unable to sum as process reported are not unique".
Thank you again for your help.
ASKER
I added "P" at the beginning of the string in order to avoid this problem with number above 9. and it works.
Even if process is repeated twice the sum will be performed once.
Thank you again for your help.
Even if process is repeated twice the sum will be performed once.
Thank you again for your help.
I have included previous version as well on sheet1
Sumif-based-on-semi-colon-separator.xlsx