3d mapping complex formula

[Ronderbecke]

I was hoping someone could help with this (maybe it's not even possible?), but currently I have a formula that I can use that works perfect with sumproduct:

=SUMPRODUCT(IF(MOD(ROW('Travis Jones'!C12:C)-ROW('Travis Jones'!C12),12)=0,IF(ISNUMBER('Travis Jones'!C12:C),IF('Travis Jones'!H7:H="Stacie Getsinger",IF('Travis Jones'!C12:C-DAY('Travis Jones'!C12:C)+1=DATE(2017,7,1),'Travis Jones'!C4:C<>0)))))

The problem is that I have to redo 'Travis Jones'! 28 times and that number will continue to increase and I have to add every time it goes up and I use this formula quite a bit. Is there a way to make it do this exact same formula but across all the sheets?

I have already tried:

=SUMPRODUCT(IF(MOD(ROW('Travis Jones:Dustin White'!C12:C)-ROW('Travis Jones:Dustin White'!C12),12)=0,IF(ISNUMBER('Travis Jones:Dustin White'!C12:C),IF('Travis Jones:Dustin White'!H7:H="Stacie Getsinger",IF('Travis Jones:Dustin White'!C12:C-DAY('Travis Jones:Dustin White'!C12:C)+1=DATE(2017,7,1),'Travis Jones:Dustin White'!C4:C<>0)))))

That doesn't work. (Travis jones is the 1st sheet in a long line of sheets and Dustin White is the last one.

Is what I am looking to do even possible or do I just have to type in the formula a bunch of times which is extremely time consuming?

 

[XOR LX]

Hi,

Can you just confirm the formula you posted?

'Travis Jones'!C12:C and Travis Jones'!C4:C, for example, are invalid syntaxes for range references. Either include an end row reference, e.g. 'Travis Jones'!C12:C1000 or else refer to entire columns, e.g. 'Travis Jones'!C:C (though that would be strongly inadvisable within such a formula).

Regards

 

[Ronderbecke]

Hey There,

Sorry, yes I am not using a range because it continues to grow. This is all being done in Google Sheets so you can use an infinite range. Currently you can use C12:C1000 and it would work, but every day I would have to continually modify it to C12:C1012, C12:C1024, etc. Thats why its set to continue to process no matter how much each sheet grows. If your using strictly excel, then you can put the 1000 on there to reference it and make a correct working formula though.

 

[XOR LX]

Ok, thanks. I'm not that familiar with Google Sheets, but am happy to try to give you an equivalent in Excel.

Just one further question: how does this 'infinite range' syntax work with ranges which begin on different rows? Your formula uses three such ranges: C12:C, H7:H and C4:C. I presume this means that the first input to consider looks at row 12 in column C, row 7 in column H and row 4 in column C (this time for the count)? And so on.

This seems very strange indeed, and would certainly be very poor practice in Excel, not to mention illogical. Can you clarify?

Regards

 

[Ronderbecke]

Certainly. With them being IF statements, its simply verifying that they're true! Its checking C12, C24, C36, etc... is a date in the date range specified and is in fact a number. It's also verifying the H7, H19, H31, etc.... is "stacie Getsinger". If all those are correct, it would either kick back the sum of C4, C16, C28, etc.... or in this case numerically changing each to a value of 1 to show a total number of sales. Does that make sense?

 

[XOR LX]

Ok, I'm afraid I have no idea whether this would work in Google Sheets or not, but in Excel I would do the following:

First go to Name Manager and define:

Name: SheetList
Refers to: ={"Travis Jones","Joe Bloggs","Dustin White"}

(Or whatever happen to be the sheet names in question.)

With a reduced range of 89 rows, the required formula is then:

=SUMPRODUCT(N(T(OFFSET(INDIRECT("'"&SheetList&"'!H7"),12*(ROW(INDIRECT("1:"&ROUNDUP(ROWS(C12:C100)/12,0)))-1),))="Stacie Getsinger"),N(MONTH(N(OFFSET(INDIRECT("'"&SheetList&"'!C12"),12*(ROW(INDIRECT("1:"&ROUNDUP(ROWS(C12:C100)/12,0)))-1),)))=7),N(OFFSET(INDIRECT("'"&SheetList&"'!C4"),12*(ROW(INDIRECT("1:"&ROUNDUP(ROWS(C12:C100)/12,0)))-1),)))

Notes:

1) Checking, for a given date x, whether the expression x-DAY(x)=DATE(2017,7,1) would seem to be identical to asking whether MONTH(x)=July. Does Google Sheets not have a MONTH function? In any case, I used this logic to reduce the processing time.

2) Testing for numericalness of entries within column C is superfluous provided that the condition above does not pertain to the month of January.

Regards

 

[Ronderbecke]

If you can explain what the purpose of this formula is, I could potentially modify it to work in sheets? Currently, rounding is unimportant in sheets due to the fact that it will be an endless stream. I can pull the "roundup" portion out along with the finite number of cells that the formula goes to. Removing roundup and the 1 integer part of this function, i'm left with a formula parse error? I'm confused somewhat on the way it would know if every 12th cell (starting with H7) is "stacie getsinger" based on this equation? I am probably missing something here, but I am stuck with a formula error. Any thoughts?

 

[XOR LX]

If you can explain what the purpose of this formula is, I could potentially modify it to work in sheets?

It has precisely the same purpose and gives precisely the same results as the formula you posted, though over multiple worksheets.

Currently, rounding is unimportant in sheets due to the fact that it will be an endless stream. I can pull the "roundup" portion out along with the finite number of cells that the formula goes to. Removing roundup and the 1 integer part of this function, i'm left with a formula parse error?

Unless the ranges you reference have a number of rows which is a multiple of 12, then the ROUNDUP function will be necessary so as to prevent a non-integer value being passed to the ROW function. I'm not sure what you mean by removing "the 1 integer part of this function", though in general I'm not surprised if a formula no longer works as intended if certain parts are removed arbitrarily!

I'm confused somewhat on the way it would know if every 12th cell (starting with H7) is "stacie getsinger" based on this equation?

The part:

12*(ROW(INDIRECT("1:"&ROUNDUP(ROWS(C12:C100)/12,0)))-1)

resolves to:

{0;12;24;36;48;60;72;84}

and, passed to OFFSET with an initial reference of, say, H7, resolves to the array of cell references:

{H7;H19;H31;H43;H55;H67;H79;H91}

Regards

 

[Ronderbecke]

When I copy the formula over it comes back with a value of 0 which is why I was trying to modify it. Not sure why it's not bringing back the values. When I use the original it brings back the 1 on that sheet, with the new formula it shows a value of 0 though.

 

[Ronderbecke]

Not sure if this will help, but the sheet has customer blocks which are 12 rows from A to H. This is what it looks like:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 66px"><col width="82"><col width="75"><col width="85"><col width="78"><col width="90"><col width="90"><col width="114"></colgroup><tbody>[TR]
[TD="colspan: 2, align: center"]Customer Name:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , colspan: 4, align: center"]Douglas Rogers[/TD]
[TD]Additions:[/TD]
[TD]Ground Mount[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Financing Partner:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , colspan: 2, align: center"]Dividend[/TD]
[TD="align: right"]11%[/TD]
[TD]1 Soladeck[/TD]
[TD]Trenching:[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: right"]Required Installation Charges: [/TD]
[TD]Charge:[/TD]
[TD]Cost:[/TD]
[TD]Profit:[/TD]
[TD]Shingle[/TD]
[TD]Dirt (in feet):[/TD]
[/TR]
[TR]
[TD="colspan: 2, align: center"]System Size:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]5.61[/TD]
[TD="align: right"]$23,393.70[/TD]
[TD="align: right"]$18,661.81[/TD]
[TD="align: right"]$2,992.79[/TD]
[TD]☑Roof Tilt[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]$4.17[/TD]
[TD="align: center"]<- P PPW C->[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]$4.17[/TD]
[TD="align: right"]Rep Payout:[/TD]
[TD="align: right"]$1,739.10[/TD]
[TD="align: right"]LG PANELS[/TD]
[TD]Sent Date:[/TD]
[TD]Concrete (in feet):[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: right"]Energy Efficiency Package:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]$609.30[/TD]
[TD="align: right"]$442.02[/TD]
[TD="align: right"]$167.28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]12/15/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: right"]Company Cash Incentive:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]$2,400.00[/TD]
[TD="align: right"]$2,264.00[/TD]
[TD="align: right"]$136.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]12/12/2017[/TD]
[TD]Stacie Getsinger[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: right"]Tankless Water Heater:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] "][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2, align: right"]HVAC:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]None[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] "][/TD]
[TD]PTO Date:[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: right"]Additional Charge:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]$0.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] , align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] [/URL] "][/TD]
[TD="align: right"]12/13/2017[/TD]
[/TR]
[TR]
[TD]Approved:[/TD]
[TD="align: right"]7/12/2017[/TD]
[TD="align: right"]TOTAL:[/TD]
[TD="align: right"]$26,403.00[/TD]
[TD="align: right"]$23,106.93[/TD]
[TD="align: right"]$3,296.07[/TD]
[TD]Sale - Install:[/TD]
[TD]Install - PTO:[/TD]
[/TR]
[TR]
[TD="colspan: 2, align: center"]Sale Date:[/TD]
[TD="align: right"]7/11/2017[/TD]
[TD="colspan: 2, align: center"]Installation Date:[/TD]
[TD="align: right"]11/29/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: right"]141[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]