SumProduct Date Comparison Double Unary Operator

[tcardwell]

Hello,

Was trying to optimize a sumproduct formula that uses date comparison conditions. The following formula works fine:

=SUMPRODUCT(SUMIFS(PerAct,Entity,--(INDIRECT("EntLst")),Date,INDIRECT("D1"),Date, INDIRECT("D2"),Account,--(INDIRECT($A4))))

D1 is a cell with a date formatted as >=04/30/2016 and
D2 is a cell with a date formatted as <= 05/31/16.

But when I add --(indirect("D1")) and --(indirect("D2") to the above formula it does not yield accurate results only zeros.

PerAct, Entity, Date and Account are named ranges in a table. Does anyone have any ideas on why the date range doesn't want to be optimized?

Thanks, Tom C

 

[StephenCrump]

I'm not sure why you have all the INDIRECT's.

I'd do the date bit like this:

=SUMPRODUCT(Something,--(YourDate>=D1),--(YourDate<=D2))

where D1 and D2 are the required start date and end date.

What are doing with Entity and Account? Perhaps checking if they're on the relevant list?

 

[tcardwell]

Hello and thanks for the reply,

Yes, Entity and Account are named ranges and, depending on the criteria, will determine which records will be selected and that part of the formula is working fine. I wanted to do something similar with the dates since the table is 12,000 rows and I only want to sum the records that meet the date criteria. The date comparison logic works fine without the unary optimizers, I was just wondering why it doesn't work when I add the double unary. I need all the indirects to point to the criteria to the underlying arrays.

 

[FDibbins]

Why not just use SUMIFS?

=SUMIFS(PerAct,Entity,"EntLst",Date,">="&D1,Date, "<="&D2,Account,$A4)

 

[StephenCrump]

Why not just use SUMIFS?

Yep! I'm assuming the task is something like:

B6: =SUMIFS(G2:G10,D2:D10,Entity,E2:E10,Account,F2:F10,">="&StartDate,F2:F10,"<=" &EndDate)

	A	B	C	D	E	F	G
Entity	Entity
Account	ABC
Start	ABC
End
ABC
XYZ
ABC
XYZ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]XYZ[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Account[/TD]
[TD="align: right"]Date[/TD]
[TD="align: right"]Amount[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]23 Apr 2016[/TD]
[TD="align: right"]$13[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]1 May 2016[/TD]
[TD="align: right"][/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]28 Apr 2016[/TD]
[TD="align: right"]$77[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]31 May 2016[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]XYZ[/TD]
[TD="bgcolor: #FFFF00, align: right"]100[/TD]
[TD="bgcolor: #FFFF00, align: right"]3 May 2016[/TD]
[TD="bgcolor: #FFFF00, align: right"]$13[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]8 May 2016[/TD]
[TD="align: right"]$15[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFF00"]Total[/TD]
[TD="bgcolor: #FFFF00, align: right"]$21[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]XYZ[/TD]
[TD="bgcolor: #FFFF00, align: right"]100[/TD]
[TD="bgcolor: #FFFF00, align: right"]13 May 2016[/TD]
[TD="bgcolor: #FFFF00, align: right"]$1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]18 May 2016[/TD]
[TD="align: right"]$30[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]XYZ[/TD]
[TD="bgcolor: #FFFF00, align: right"]100[/TD]
[TD="bgcolor: #FFFF00, align: right"]23 May 2016[/TD]
[TD="bgcolor: #FFFF00, align: right"]$7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]28 May 2016[/TD]
[TD="align: right"]$94[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]2 Jun 2016[/TD]
[TD="align: right"]$46[/TD]

</tbody>

​

 

[Jonmo1]

Yeah, I don't understand the use of indirect or the -- operations either..

I think this is what you're trying to do.

=SUMPRODUCT(SUMIFS(PerAct,Entity,EntLst,Date,D1,Date, D2,Account,$A4))

 

[tcardwell]

Thank you Mr Crump, Mr Dibbins and Mr Jonmo1,

I tried each of your suggestions but to no avail. I was able to shorten up the function and the following seems to work best:

=SUMPRODUCT(SUMIFS(PerAct,Entity,--(INDIRECT("EntLst")),Date,D$1,Date,D$2,Account,--(INDIRECT($A4))))

Not sure if I made this clear but EntLst is a named range that can contain up to a list of 5 entities. So, as I understand it, I needed the Indirect wrapper to point to the underlying range/array that contains the list of entities. Also, A4 is a cell that contains a word that is a named range that points to all the accounts that can be summed up into that line on the P&L statement. Ultimately I am building a P&L statement from a trial balance. The trial balance has as column headers: Account, Desc, BegBal, Activity, EndBal, Entity, Date, which I turned into Named Ranges. Right now this file has about 12k rows of data so I was wanting to make it run as fast as possible. Thanks to your help I was able to shorten the Date conditions, which as it turned out, did not need the Indirect wrapper function.

Thanks, Tom C