# Need to find column matches and total values from corresponding rows

1. ## Need to find column matches and total values from corresponding rows

I've attached a worksheet showing my table with the relevant column info displayed.

The formula would be located in column Q and needs to be able to look at columns I,J and L and find rows where all three values are the same.
Where it finds rows with all of these values matched it should then total the corresponding values in column O. I have provided the result
that I expect throughout column O.

Example: Q3 is totalling O3,O4 and O5 because the formula would have found that columns I,J and L contain the same information on rows 3,4,5

I'm not sure where I'd actually start with creating this formula. Whether a LOOKUP would suffice or whether I'd need to INDEX & MATCH. Can
anyone point me in the right direction? Thanks.

2. ## Re: Need to find column matches and total values from corresponding rows

In Q3, then copied down:

=SUMIFS(O:O,I:I, I3,J:J, J3,L:L, L3)

3. ## Re: Need to find column matches and total values from corresponding rows

Thanks JBeaucaire. I originally posted a simpler version of the formula I actually intended to create, as I thought I'd be able to use the principles in
the formula that you suggested, =SUMIFS(O:O,I:I, I3,J:J, J3,L:L, L3) to then adapt it into the actual formula I am after. It turns out that
it is more complicated than I thought.

I have re-attached an updated version of the spreadsheet with my actual current formula in column Q, which shows the results that I want returned.
Again, the formula needs to be able to look at columns I,J and L and find rows where all three values are the same.
Where it finds rows with all of these values matched it should then apply the formula only to those rows. I have provided the result
that I expect throughout column Q.

Commonly, matches would be found over 1, 2 or 3 rows and this is where it gets complicated for me, having to create a dynamic formula. If anyone has any suggestions,
they'd be greatly appreciated. Thanks.

Dan

4. ## Re: Need to find column matches and total values from corresponding rows

Hi Dan,

Am not sure what the reference to 'column U' is for. In any case, please refer to column R that should yield you the results you are after.

5. ## Re: Need to find column matches and total values from corresponding rows

Thanks Ace_XL. That's exactly what I am after. I've just tried to apply these principles to a new column 'Yield Total', but can't make sense of your original
formula in Q3 and why it works to even begin with. I've updated the workbook and re-attached it. In a few cells below the table I tried to break down the formula
from Q3, but, for some reason the result comes out as 34.97% and not 4.37%.

Anyway, what I wanted to do was to gain the results as displayed in the 'Yield Total' column (highlighted in yellow). Again, I have made the raw calculations that should
be made if the original conditons are met; "look at columns I,J and L and find rows where all three values are the same. Where it finds rows with all of these values
matched it should then apply the formula only to those rows."

Notice that a result is only returned in the last row of the rows where the 3 matches are found (as with column P). In 'column P' I used the following formula and want to incorporate
it into this formula.

=IF(SUMIFS(O:O,I:I,I5,J:J,J5,L:L,L5)=SUMIFS(O:O,I:I,I6,J:J,J6,L:L,L6),"--",Otherwise make calculation)

Thanks again.

6. ## Re: Need to find column matches and total values from corresponding rows

Have populated desired results in (Column 'T') with the aid of a helper column (Column 'S'). let me know if this helps!

7. ## Re: Need to find column matches and total values from corresponding rows

Thanks again Ace_XL, that's very very helpful and saves me a lot of time. I really appreciate your help. Thanks.

Dan

8. ## Re: Need to find column matches and total values from corresponding rows

Is it possible to adjust columns P, Q and T so that they only reference the area within the table. At present I:I, J:J, L:L and O:O are referenced which cause problems when I duplicate the table below (I intend to do this multiple times). I tried to adjust for example, I:I to \$I\$3:\$I\$23, but this would mean that any new duplicates of the table will continue to reference \$I\$3:\$I\$23 and not the new area of the duplicated table. If I manually adjusted each row of the formula to reference I3:I23 (without '\$'), I'd be able to copy my table no problem and everthing would reference correctly, but if I wanted to add new rows to the table, the formula would not copy down automatically because each rows formula would be unique.

Is there a way around this? Thanks.

9. ## Re: Need to find column matches and total values from corresponding rows

I'd suggest assigning separate names to the arrays in Columns I,J and O. In that case, if you are creating a duplicate table all that you would need to change is the reference to the arrays.

10. ## Re: Need to find column matches and total values from corresponding rows

I've assigned names to the arrays using the Name Manager, adapted the formulas so that they reference these arrays (and not the entire columns), and then copied a table down as a test.
The new table's formulas are referencing the 1st tables named ranges as expected.

This is an example of one of the original formulas for the 1st table:
=IF(Q11="","--",SUM((((R11*(Q11-1))*(100%-Y11))-SUMIFS(R:R,L:L,L11,M:M,M11,O:O,O11)+R11)/SUMIFS(R:R,L:L,L11,M:M,M11,O:O,O11)))

This is an example of one of the adapted formulas for the 1st table:
=IF(Q11="","--",SUM((((R11*(Q11-1))*(100%-Y11))-SUMIFS(Tab1R,Tab1L,L11,Tab1M,M11,Tab1O,O11)+R11)/SUMIFS(Tab1R,Tab1L,L11,Tab1M,M11,Tab1O,O11)))

I've named ranges in the following way:
1st Table Column R:R = Tab1R
1st Table Column L:L = Tab1L
1st Table Column M:M = Tab1M
1st Table Column O:O = Tab1O

Are you suggesting that I create named ranges for the 2nd table, lets say, as follows:
Tab2R, Tab2L, Tab2M, Tab2O

and then manually edit the above formula in the 2nd table to reference these new named ranges?

I plan on duplicating and using new tables possibly 2 times a day, archiving used tables on another sheet and also giving the sheet to a friend to use who is not very competent in excel. Ideally he would be able to just
copy and paste the table if and when required without additional work.

Is it possible to use the original formula, but restrict it to only make calculations in the table that the forumla resides of ranges R:R,L:L,M:M and O:O?

If not, I could manually give each table a number in the header row and create another helper column that would look up this number. So, for instance Table 1 would have a column with all 1's in and the formula would only
consider rows with a 1 in the new helper column. Ideally if it's possible for the ranges to only total the table in question, that would be preferable. Thanks.

11. ## Re: Need to find column matches and total values from corresponding rows

I'd suggest changing the array of the 1st Table to the following assuming that the all your original (Table1) data resides between Row 1-23

1st Table Column R1:R23 = Tab1R
1st Table Column L1:L23 = Tab1L
1st Table Column M1:M23 = Tab1M
1st Table Column O1:O23 = Tab1O

Post this, (as you correctly thought of) create named ranges for the 2nd table - Tab2R, Tab2L, Tab2M, Tab2O. However the array for these would be (say) from Row 51-73 instead of the 1-23 used in Table 1 ranges.

This way once you copy data from Table 1 to Table 2, all you need is to ensure that instead of using Tab1R,Tab1L etc. the corresponding formula cells for this second table refer to Tab2R, Tab2L etc.

Hope this makes sense!

12. ## Re: Need to find column matches and total values from corresponding rows

Sorry, my last post was not explained very well. I have assigned the following ranges these names already.

1st Table Column R1:R23 = Tab1R
1st Table Column L1:L23 = Tab1L
1st Table Column M1:M23 = Tab1M
1st Table Column O1:O23 = Tab1O

I was just looking for another solution other than this because ideally I just want to copy and paste a table and have no further work to do.

An example of my set up is:
'Sheet1' contains the standard table. Over the period of a morning I fill out rows in the table and then it is completed. I want to archive this table and so copy and paste it below. I then clear all the data entries from the 1st table so that I can start a fresh with new information. Once completed, I would then copy and paste this table below the 2nd. The process continues.

If I were to use named ranges I will have to create new named ranges for each table I archive. That's why I was asking if it was possible to use the original formulas that reference the entire columns, but limit them to only considering data in the table where the formula resides. If not, I'd go down the route of creating another Helper Column (see bottom of my previous post). Thanks.

13. ## Re: Need to find column matches and total values from corresponding rows

Just a couple of cheeky solutions to save all the trouble with changing names and ranges..

1. Instead of saving the archive data below the table, couldn't you do this alongside (to the right of the worksheet)?
2. Save each archive as a separate tab in the worksheet?

Let me know.

14. ## Re: Need to find column matches and total values from corresponding rows

Option 1
1. Instead of saving the archive data below the table, couldn't you do this alongside (to the right of the worksheet)?

Archive data would ideally be saved in a seperate tab in the workbook. I would like the 1st worksheet to have 10 duplicates of the table ready incase I want to have 10 tables in use at one time. When a table is completed I would then want to archive it. Once a table is archived (wherever it may be put) I would want to reset that original table to blank so that it is ready to be used again.

Duplicating my 10 tables on the main sheet to the right of each other instead of below could be an option, but it is a lot easier to scroll down through the tables I am currently using than across to the right. I could create hyperlinks, but still scrolling down is a lot more user friendly.

I could create a seperate tab for the archived tables and copy them side by side, but I intend to analyse and compile data on these tables at a later date. Wouldn't it be easier to do this if the tables were archived vertically rather than horizontally?

Option 2
2. Save each archive as a separate tab in the worksheet?

Wouldn't saving each archive as a seperate tab in the workbook use up a large amount of file space if I have created say, 500 or more tabs over a few months? Would it make it hard to analyse the archived results if they are all on seperate tabs?

Saving each archived table to a seperate tab, I would still not have solved the problem of wanting 10 tables for current use on the same tab.

The ideal situation which would allow simple viewing and simple copy and pasting of the tables, would ideally be to have the 1st tab for the 10 tables in current use aligned vertically and the 2nd tab for archived tables with the tables aligned vertically also, if this is possible? As I say, I could create another helper column that would make each table unique to solve the problem.

15. ## Re: Need to find column matches and total values from corresponding rows

Hmm..separate name ranges is out of the question due to the number of tables you'll end up with!

Still being cheeky to save creating some monstrous formulae...so bear with me

Instead of copying & pasting the archive table right below the preceding table, try pasting it in just one column to the right (alongwith the table headers). Do the same every single time you archive a table. I understand this will not perfectly align with your top table and create a cascading view leaning towards the right of the screen. Will still serve the purpose though and not distort viewing completely.

Sample worksheet attached.

16. ## Re: Need to find column matches and total values from corresponding rows

Thanks Ace_XL. I went with your original idea of creating named ranges. I created named ranges for 20 tables, with 19 tables duplicated below the 1st. When I have completed these tables I will copy them to an archive tab and then clear the originals and start again. Thanks for your help, it's greatly appreciated.

Dan

17. ## Re: Need to find column matches and total values from corresponding rows

Tedious yet effective solution under the circumstances. Glad to help!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1