Need help with dynamic ranges and conditional sum/SUMPRODUCT formulas

[shstrating]

For some reason I cannot get my head wrapped around what I need to do to make this happen, and after 3 fruitless hours of researching innumerable posts in this forum I have to resort to posting my question because I cannot figure out how to make the answers already supplied here work.

Software: WinXP SP2 and Excel 2003 SP3

I have a workbook consisting of one worksheet on which I am performing many calculations. These calculations pull data from the remaining worksheets in the workbook, all of which are the result of CSV data dumps from a Customer Relationship Management(CRM) system. These data worksheets are called things like Sales Calls, Referrals, New Opptys and consist of both alpha and numeric data. These data dumps are performed at least monthly, if not more often, and the number of records changes regularly(at least it had better or else the Sales Reps are going to have to answer some tough questions as to why they aren't making sales related calls ). There is no guarantee that the data fields on a given worksheet are fully populated with values; in other words there could be blank cells and they could be anywhere.

On my calculation worksheet (Calc) I am using various =SUM(IF...) and =SUMPRODUCT(...) formulas to arrive at totals for how each Sales Rep is performing as well as how each affiliate office is performing (made up of one or more Sales Reps). Currently I am 'hard-coding' the ranges into the SUM(IF...) and SUMPRODUCT(...) formulas, but as you can imagine that becomes a maintenance nightmare because they have to be updated every time a new data dump is performed (or at least checked to see if the size of the range changed).

The way the data dumps are performed is that the someone exports the CSV file out of the CRM system and directly into an Excel file. This person then opens the Excel file and performs a simple Copy All and the data is pasted into my workbook, overwriting the existing data on the appropriate worksheet. This is very manual, but it's the way they want it done and as long as I don't have to do it I don't care.

The number of columns should stay consistent from dump to dump while the number of rows changes. I say the columns "should" stay consistent because I assume it is possible someone may structure their query a bit differently in CRM and end up with more or less columns. If it's possible to account for different numbers of rows AND columns then that would be ideal. (Heaven forbid they export the files with a different column order because then I'm royally screwed and someone will be getting a visit from my very large and angry friends Bruno and Vinnie.)

What I am trying to figure out is how to build my SUM(IF...) and SUMPRODUCT(...) formulas such that they properly detect and account for the flucuating data ranges that are being regularly pasted into the Sales Calls, Referrals, etc. worksheets.

An example of one of my formulas (located on the Calc sheet in cell F4) is:
{=SUM(IF('Sales Calls'!$E$2:$E$4136=$A4,'Sales Calls'!$J$2:$J$4136,0))}
where:
$A4 contains a Sales Rep's name
'Sales Calls'!$E2:$E4136 contains a list of all Sales Reps' names
'Sales Calls'!$J2:$J4136 contains a tally of sales calls made by each Sales Rep

I want to make the E:E and J:J ranges dynamic to match the ever changing data.

I know I'm making this way harder than it is, and I'm hoping someone can help me get a grip on what I need to do.

Thanks for whatever guidance you can provide.

Steve

 

[Aladin Akyurek]

Define Size by means of Insert|Name|Define as referring to:

=MATCH(9.99999999999999E+307,'Sales Calls'!$J:$J)-ROW('Sales Calls'!$J$2)+1

Define CALLS as referring to:

=OFFSET('Sales Calls'!$J$2,0,0,Size)

Define REPS as referring to:

=OFFSET('Sales Calls'!$E2,0,0,Size)

 

[shstrating]

Aladin,

Thank you very much for the reply. It was one of your previous responses to a question like this that I was trying to adapt to my situation, but for some reason I just couldn't get past a mental roadblock to the solution.

I think I understand what I have to do now, but if you happen to read this and are willing to confirm my understanding I would appreciate it.

With the Names you've given me defined through the Insert > Names > Define dialog, my example formula would then become:
{=SUM(IF(REPS=$A4,CALLS,0))}

Is that correct?

I also am a bit confused on the use of MATCH to find the end of the range, and whether it account for blanks and/or mixed alpha and numeric entries in a list. I know that the data coming from CRM could have some "0" entries (w/o quotes) that may be entered into the CALLS range as Text rather than Numbers.

I'm trying to decipher what your MATCH formula is doing. I know the first value is what you have had people define in the past as BigNum; which I think is meant to be a numeric value so large that it is virtually guaranteed to always be larger than any number anyone might be working with in their spreadsheets. I also know that the match_type is inferred to be "1" when it is left out, so MATCH is looking for the largest value in the second value <= the first value. What I don't get is how defining the second value as 'Sales Calls'!$J:$J finds the end of the range and what value MATCH actually returns.

I'm also trying to understand the rest of that Size formula. Once MATCH returns a value (let's say 1000), then you're having it subtract the row reference for cell $J$2 (which I assume is 2), and then it's adding back 1. So in my example using 1000, it would be 1000-2+1=999. Why wouldn't you just subtract 1 from the result of MATCH? Sorry for my ignorance and I am in no way questioning the validity and necessity of the formula you've provided, I'm just trying to understand the "Why" behind it.

Thank you again for your help, and for your patience in assisting so many of us over the course of your membership on this board.

Best Regards,
Steve

 

[Aladin Akyurek]

Aladin,

Thank you very much for the reply. It was one of your previous responses to a question like this that I was trying to adapt to my situation, but for some reason I just couldn't get past a mental roadblock to the solution.

I think I understand what I have to do now, but if you happen to read this and are willing to confirm my understanding I would appreciate it.

With the Names you've given me defined through the Insert > Names > Define dialog, my example formula would then become:
{=SUM(IF(REPS=$A4,CALLS,0))}

Is that correct?

Yes if A4 houses a rep. Btw, I'd use here a SumIf formula:

=SUMIF(REPS,$A4,CALLS)

I also am a bit confused on the use of MATCH to find the end of the range, and whether it account for blanks and/or mixed alpha and numeric entries in a list. I know that the data coming from CRM could have some "0" entries (w/o quotes) that may be entered into the CALLS range as Text rather than Numbers.

That Match bit for determining Size really requires a numeric range/reference. Try to apply it to such a range.

I'm trying to decipher what your MATCH formula is doing. I know the first value is what you have had people define in the past as BigNum; which I think is meant to be a numeric value so large that it is virtually guaranteed to always be larger than any number anyone might be working with in their spreadsheets. I also know that the match_type is inferred to be "1" when it is left out, so MATCH is looking for the largest value in the second value <= the first value. What I don't get is how defining the second value as 'Sales Calls'!$J:$J finds the end of the range and what value MATCH actually returns.

You are almost there. Maybe the following link might help:

http://www.mrexcel.com/forum/showthread.php?t=310278

I'm also trying to understand the rest of that Size formula. Once MATCH returns a value (let's say 1000), then you're having it subtract the row reference for cell $J$2 (which I assume is 2), and then it's adding back 1. So in my example using 1000, it would be 1000-2+1=999. Why wouldn't you just subtract 1 from the result of MATCH? Sorry for my ignorance and I am in no way questioning the validity and necessity of the formula you've provided, I'm just trying to understand the "Why" behind it.

A2=5
A3=0
A4 empty
A5=7

Size here would be 4.

MATCH(BigNum,A:A) === 5

5-ROW(A2)+1 === 5-{2}+1 === 4

Thank you again for your help, and for your patience in assisting so many of us over the course of your membership on this board.

Best Regards,
Steve

Steve, you are welcome.

 

[shstrating]

Aladin,

Thank you for the explanation and the link. I think I have it, but will have to ponder it a bit more before I'm confident explaining it to my boss, who will have to have confidence that she can trust a dynamic range solution.

You know how it goes anytime we 'automate' anything; people get extremely nervous that it's going to automate them into a bunch of problems that they won't catch until it's too late.

Best regards,
Steve