UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Array Formula Not Working, Office 2013    
 
   
justair07
post Sep 7 2018, 07:58 AM
Post#1



Posts: 759
Joined: 22-August 13



Hello,

I use array formulas all the time for linked formulas that I want to update even when the workbook is closed. This works great.

For some reason my array formula does not work when I try to reference data from a workbook that uses Power Query.

Here is my formula:
CODE
{=SUMIFS('[Data 061218.xlsx]Sheet2'!$I:$I,'[Data 061218.xlsx]Sheet2'!$Q:$Q,G$2,'[Data 061218.xlsx]Sheet2'!$E:$E,$B18,'[Data 061218.xlsx]Sheet2'!$H:$H,$A18)*INT($B18<TODAY())}


Thank you,

Justin

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 
Debaser
post Oct 11 2018, 01:06 AM
Post#2



Posts: 6
Joined: 11-October 18



In what way doesn’t it work? Also, I can’t see any need for that to be an array formula.
Go to the top of the page
 
justair07
post Oct 11 2018, 08:59 AM
Post#3



Posts: 759
Joined: 22-August 13



Hi Debaser,

QUOTE
I use array formulas all the time for linked formulas that I want to update even when the workbook is closed.


Array formulas are the only way to accomplish this with a formula...
This post has been edited by justair07: Oct 11 2018, 09:00 AM

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 
Debaser
post Oct 11 2018, 09:30 AM
Post#4



Posts: 6
Joined: 11-October 18



That is not actually true. Even if it were, SUMIFS doesn't work with a closed workbook, no matter how you enter it.
Go to the top of the page
 
justair07
post Oct 11 2018, 09:45 AM
Post#5



Posts: 759
Joined: 22-August 13



I think that's my problem, I need to use a sum(if not sumif in an array to accomplish this.

Which part isn't true? From my experience you have to use array formulas to update workbooks where the data source is in a closed workbook.

If you disagree please share an alternative solution through formulas.

Thank you,

- Justin

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 
Debaser
post Oct 11 2018, 09:53 AM
Post#6



Posts: 6
Joined: 11-October 18



You can use SUMPRODUCT with closed workbooks to work like SUMIF(S) - or COUNTIF(S) without array-entering it.

Most regular formulas also work with closed workbooks without array entry, but perhaps you were specifically referring to this sort of sum by criteria formula.
Go to the top of the page
 
justair07
post Oct 11 2018, 12:02 PM
Post#7



Posts: 759
Joined: 22-August 13



Ahh that makes sense. Thank you!

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 04:23 AM