formula for lookups and sum ifs

[jhampel106]

I am looking for someone who is proficient in formulas to help me out. I am trying to match a date range and total sums if the row falls between the date range. I want to do this as a formula, not a macro. My file has three worksheets. In worksheet 'PT', column E (Line Description) lists the beginning and ending date range, but it is in text format with other characters at the end of the line, but the first 6 characters will always be beginning date and characters 8-13 will always be the ending date. Worksheet 'names' cross references the common data that will match the other two worksheets. Worksheet 'UNIV' lists all the raw data that needs to be totaled. I would like to create a formula in worksheet 'PT' where it grabs every line in worksheet 'UNIV' that falls within the date range for that customer and total it in the highlighted cell D2. Can someone help me?

Worksheet 'names':[TABLE="width: 175"]
<TBODY>[TR]
[TD]Customer ID</SPAN>
[/TD]
[TD]Distributor</SPAN>
[/TD]
[/TR]
[TR]
[TD]BeeBee Co.</SPAN>
[/TD]
[TD]BB</SPAN>
[/TD]
[/TR]
[TR]
[TD]Feezer</SPAN>
[/TD]
[TD]Fizzer</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Worksheet 'UNIV':

[TABLE="width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 280"]
<TBODY>[TR]
[TD="width: 48, bgcolor: transparent"]RA Name
[/TD]
[TD="width: 186, bgcolor: transparent"]Distributor
[/TD]
[TD="width: 74, bgcolor: transparent"]Inv. Date
[/TD]
[TD="width: 64, bgcolor: transparent"]Discount
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]BB
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7/29/2013
[/TD]
[TD="bgcolor: transparent, align: right"]-46.2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]BB
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7/31/2013
[/TD]
[TD="bgcolor: transparent, align: right"]-92.4
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]15
[/TD]
[TD="bgcolor: transparent"]BB
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7/31/2013
[/TD]
[TD="bgcolor: transparent, align: right"]-38.5
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]49.9
[/TD]
[TD="bgcolor: transparent"]Fizzer
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7/26/2013
[/TD]
[TD="bgcolor: transparent, align: right"]-51.36
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]57.2
[/TD]
[TD="bgcolor: transparent"]Fizzer
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7/30/2013
[/TD]
[TD="bgcolor: transparent, align: right"]-47.52
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

Worksheet 'PT'
[TABLE="width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 266"]
<TBODY>[TR]
[TD="class: xl68, width: 77, bgcolor: transparent"]Customer ID
[/TD]
[TD="class: xl68, width: 160, bgcolor: transparent"]Line Description
[/TD]
[TD="class: xl69, width: 53, bgcolor: transparent"]Amount
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]BeeBee Co.
[/TD]
[TD="class: xl63, bgcolor: transparent"]072913 080513 bal
[/TD]
[TD="class: xl67, bgcolor: transparent"]-432.63
[/TD]
[TD="class: xl71, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Feezer
[/TD]
[TD="class: xl63, bgcolor: transparent"]072613 072613 bal
[/TD]
[TD="class: xl67, bgcolor: transparent"]-51.36
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Feezer
[/TD]
[TD="class: xl63, bgcolor: transparent"]073013 080113 bal
[/TD]
[TD="class: xl67, bgcolor: transparent"]-53.46
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Feezer
[/TD]
[TD="class: xl63, bgcolor: transparent"]081213 081613 bal
[/TD]
[TD="class: xl67, bgcolor: transparent"]-953.05
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[benishiryo]

hi there. it's a little confusing without any given cell references. you mentioned Line Description is in Column E, showed the highlighted cell after Line Description in the table, and then asked to fill up D2? let me make a few assumptions to work with it.
Customer ID will be in A1 of names sheet.
RA Name in A1 of UNIV sheet
3rd table's Customer ID in A1 of PT sheet
with that, try this in D2 of PT sheet:
=SUMIFS(UNIV!$D$2:$D$6,UNIV!$C$2:$C$6,">="&DATE(20&MID(B2,5,2),LEFT(B2,2),MID(B2,3,2)),UNIV!$C$2:$C$6,"<="&DATE(20&MID(B2,12,2),MID(B2,8,2),MID(B2,10,2)),UNIV!$B$2:$B$6,VLOOKUP(A2,names!$A$2:$B$3,2,0))

 

[jhampel106]

The tables that I used in my original post did not take the hidden columns into consideration. Can you look at this link? This link is a .pdf of the three worksheets that I am using.


https://www.dropbox.com/s/j7gwpkkdisfsnk3/PublicationK12%20EX.pdf

 

[benishiryo]

why don't you try to fit my formula into your working file & then feedback to me? i've already based it on your OP

 

[jhampel106]

When I change the cell references to the actual rows it keeps returning a #VALUE message. It might be because of the hidden rows. That is why I added the link with the actual screen shots of what I am looking at. I thought it might help to get this figured out. I have been working on this for several months, and currently I do it by hand, which is a labor-intensive project. I know that there is a way to build the formula, but so far it eludes me. Any help you can give me would be greatly appreciated!!