Relating Text to Values and Selecting Specific Rows

[RTOOMEY25]

Hi, a little new to Excel and the site so don't hesitate to ask if my explanation is a bit confusing.

I have two columns that relate two panels together which are in A and B. These columns contain, in text form, circuit numbers (ex. 6. 10; 5. 13,5. 15) where 6. 10 is panel 6 circuit 10 and 5. 13,5. 15 is panel 15 circuit 13 and 15 (14 not included). I have a list on the same sheet of all of the circuit numbers (col H) in the form of 5.13 as a number and associated phases A,B, and C (col K,L,M) showing amperage numbers. (Please refer to the attachment for clarification; I have omitted information just to show an example)

I need to write a function that selects the appropriate circuit number(s) in row H from row A (i.e. find circuits 13 and 15 for panel 5 row H when it says 5. 13,5. 15) and put the sum of the associated phase number from rows K,L,M into columns C,D,E respectively. Columns A and B may vary while H will remain the constant.

For example: in row 11 on the attachment, I will need it to find the sum of the A, B, and C phases for 6.9, 6.11, and 6.13 and put that sum into the associated phases in cols. C, D, and E. C11, D11, and E11 would then read 0, 3.3, 3.3 respectively.

I then need to take the add up of all of the values in columns C, D, and E for the panel number (first number) in column B and put sum into a single cell (which is C24, D24, and E24 in the example). So in the example C3-6, C8, and C11 will need to be added and put into C24 as in column B all the text begins with "5".

Thanks for any help! Sorry if it was long winded and confusing.

[FDibbins]

Hi and welcome to the forum

I can (I think) figure out what you want, but how would you handle having on 2 "sets", would they be P1 and P2...or P32 and P3...or?

[RTOOMEY25]

Are you asking if i have two or three numbers in the column A? If so, just using the "A Phase" column as an example, the sum of column K for all two/three numbers would need to go into column C. For example the 6.9, 6.11, 6.13 row in column A would need the sum of K5, K6, and K7 to go into C11. Then the same would be done for the B and C phase (sum of columns L and M for those rows into D11 and E11). Let me know if that's not what you're asking and thanks for getting back so quickly.

[FDibbins]

OK take a look at the attached. I created a series of helper columns (C:E) to break out the different circuits (?), and then used index/match to return the values. You mentioned you wanted the values summed, but each row only has 1 number in it, the others are 0. Let me know if we are headed in the right direction?

[RTOOMEY25]

Yes this is definitely on the right track thank you. There are only two issues that, if possible, need to be resolved.

1. The value for "6.50" in row 8 matched the "6.5" value in column K. If "6.50", or any other number ending in 0, is in row A it can only return the values for "6.50" in row K and not "6.5"

2. The value that will be put into column C will not always correspond to an A Phase value (column N). I have created an example of this situation on the new attached form where "6.48" corresponds to a value in the B Phase (column O) but this number is not accounted for in row 8. You can also see an example of the conflict of the "6.50" vs. "6.5" here.

I will also need the values to the right of the "5" in C:24 to sum up the values in rows F,G, and H for each row where column B begins with a "5". So in the example I have saved here F:24, G:24, and H:24 would read 0, 6.6, 3.3 respectively as I put place holders in row 2 in those columns that would not be in that total as row B starts with a "10".

Again, thanks for all of your help!

[RTOOMEY25]

Working on this I've actually resolved #2 and the last issue. I just need help with issue #1 now if you've had a chance to take a look.