how to use lookup to sum columns

**John Bortoli** · 11-22-2016, 10:52 PM

Hi all,
I have a VBA macro used in autocad which uploads data to an excel spread sheet.
I was wondering if someone can help me.
Column B contains a number which could be 1,2,3,4 etc
I would like to be able to get VBA to lookup col B and for every instance of 1 then add the corresponding row data for columns F,H I J and place the sum for each at the side of the sheet. see attached file

In Anticipation Thanks
John B

The VBA code from autocad which loads the spreadsheet is as below

Please Login or Register  to view this content.

**David A Coop** · 11-23-2016, 01:11 AM

Hi John,

SORRY PROTONLEAH - I JUST SAW YOUR POST

First of all, you may receive a note from admin re the posting of code without the HTML code markers because the way you posted it makes it very difficult to read. However, the code seems to be irrelevant to the problem at hand.

Having said that, I don't think you need VBA code to populate the matrix at Q1:U15.
in R13 use =SUMPRODUCT(--($B$13:$B$60=$Q13)*$F$13:$F$60) and copy down
in S13 use =SUMPRODUCT(--($B$13:$B$60=$Q13)*$I$13:$I$60) and copy down
in T13 use =SUMPRODUCT(--($B$13:$B$60=$Q13)*($J$13:$J$60=T$12)) and copy right and down.

I hope this helps, please let me know!

Regards,

David

- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

**John Bortoli** · 12-13-2016, 09:23 PM

Hi where can I find edit to fix the code

**protonLeah** · 12-13-2016, 11:02 PM

comment deleted

**John Bortoli** · 12-13-2016, 11:16 PM

Hi David,
That's a simple way of looking at it and is a great help. My expertise is in AutoCAD and not Excel as you can see.
I have followed your advice and refined my results to expand my summary.
One I am stuck on is I wish to calculate the length of 50,70 and 90 duct required to each pour.
to do this we need to lookup b13-b100 that is equal to r13 then sum column F values for each pour which is equal to t12,v12 and u12. I tried but it doesn't seem to work for me.

On another note is it possible that this result sheet could be on a second sheet of the same workbook as I suspect I may need to create a template file on which autocad dumps the main data and on the second sheet the info is automatically updated.
regards
John

**protonLeah** · 12-14-2016, 12:37 AM

On Ben's summary sheet:

Galv Dct 50: (C3)

Please Login or Register  to view this content.

Length (B3): =SUM(C3:E3)

Tendon Tonnage, Kg (F3):

Please Login or Register  to view this content.

---------------------------
PourNumber (typical named range) --> B13:B69

**John Bortoli** · 12-14-2016, 06:55 PM

Hi Guys,
I have just realised that I need to add another condition to Davids answer as given in Post #2.
Column D can vary and have a value of either 12.7mm or 15.2mm.
Basically the simple formula would need to meet the following

If B13-B100=R13 and G13-G100=S12 and D13-D100=R10 then sum the values in F13-F100 that meet the criteria and place the result in cell S13

I have no idea how to manage this but am sure with some guidance will be able to make it all work.

I have re-attached my file for your information.

Your help is greatly appreciated
John

**John Bortoli** · 12-19-2016, 01:45 AM

Thanks for your help and for the birthday wishes from the forum last week.
The code in Post #1 extracts autocad attribute data opens excel at a new workbook, and dumps the data into the blank workbook which I then use save-as to save the file.
What do I need to do to this code to open an existing worksheet instead.
I now have a standard template file named post_BOM.xltm set up which I now wish to use as my base excel workbook to dump the autocad data.
Regards
John Bortoli

**David A Coop** · 01-04-2017, 11:41 PM

Sorry for the delay getting back to you. I have been very distracted over the Christmas/New Year period.

I think all you need to do as add that extra bit to the formula :
=SUMPRODUCT(--($B13:$B100=$R13),--($G13:$G100=S$12),--($D$13:$D$100=$R$10),$F13:$F100)

I hope this helps, please let me know!

Regards,

David

- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

how to use lookup to sum columns

LinkBack

Thread Tools

Rate This Thread

Display

how to use lookup to sum columns

Re: how to use lookup to sum columns

Re: how to use lookup to sum columns

Re: how to use lookup to sum columns

Re: how to use lookup to sum columns

Re: how to use lookup to sum columns

Re: how to use lookup to sum columns

Re: how to use lookup to sum columns

Re: how to use lookup to sum columns

Thread Information

Users Browsing this Thread

Similar Threads

How to lookup a value in a table using a range in the lookup columns

Creating a three way lookup with columns and row lookup matches

[SOLVED] Multiple Lookup Values rows and columns to lookup a single target column on the right end.

[SOLVED] Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

Lookup 2 columns and return value based on another 2 columns

VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple columns.

Lookup adjacent column to multiple lookup columns.

Bookmarks

Bookmarks

Posting Permissions