Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-03-2017, 03:26 PM
mwestra74 mwestra74 is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Oct 2017
Posts: 5
mwestra74 is on a distinguished road
Default Data connection problem excel/sharepoint using an SUMIF array


Hi,

I'm using the following formula: {=SUM(IF(FREQUENCY(IF(Data!C$2:C$10000&rekenen2!B$ 2:B$10000&rekenen2!D$2$10000=$F5&$B$2&$B$1,MATCH (Data!B$2:B$10000,Data!B$2:B$10000,0)),ROW(Data!B$ 2:B$10000)-ROW(Data!$B$2)+1),1))}
when I manually update the data in sheet "data" I have no problems. I go to my sharepoint, export the data to excel and with copy and paste I make the update.
When using the dataconnection method by sharepoint and excel (refreshing), the result of my formula becomes #NA .

What could be the issue?

Regards,

Martin
Reply With Quote
  #2  
Old 10-03-2017, 11:02 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 60
ArviLaanemets is on a distinguished road
Default

In your Excel version, you can use SUMIFS (NB! SUMIFS, not SUMIF) function instead of array formula - try this.
Reply With Quote
  #3  
Old 10-04-2017, 12:46 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

I suspect you have a data type issue with the copy and paste - perhaps one column is supposed to be numeric (that would include dates) but is actually coming in as text.
Reply With Quote
  #4  
Old 10-04-2017, 03:51 PM
mwestra74 mwestra74 is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Oct 2017
Posts: 5
mwestra74 is on a distinguished road
Default

@ArviLaanements:
When using sumifs I get all the rows containing the data from F5, B1 and B2. But there are some rows with the same date that must be considered as 1.
i.e.
row 1 19JAN17
row 2 20JAN17
row 3 20JAN17

With sumifs I get 3, but it must be 2


@Debaser
when manually exporting the data out of my sharepoint to excel and than copy, paste it into my calculating file (on tab data) there are no problems.
When using the dataconnection refresh function (importing the same sharepoint columns with the same data type) the formula returns #NA
Reply With Quote
  #5  
Old 10-05-2017, 04:25 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

If copy and paste works, but a refresh doesn't then I am almost certain that your query is returning a number field as text.
Reply With Quote
  #6  
Old 10-06-2017, 12:48 AM
mwestra74 mwestra74 is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Oct 2017
Posts: 5
mwestra74 is on a distinguished road
Default

Quote:
Originally Posted by Debaser View Post
If copy and paste works, but a refresh doesn't then I am almost certain that your query is returning a number field as text.
Looked at it but both columns B and C have the same format in excel and my sharepoint. What I did found is that after refreshing, the array changes. The data range at the tab data changes from 10000 to 10045. Even when I changed the range to 5000, it changed to 5045.

Any idea this happens?
Reply With Quote
  #7  
Old 10-06-2017, 01:36 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

I'd assume the table is growing. Given that you have a table, you might as well use table references. The #N/A means there aren't any values meeting the criteria. If there should be, which seems to be the case if copying and pasting the same data produces results, then there must be a formatting difference.
Reply With Quote
  #8  
Old 10-06-2017, 05:29 AM
mwestra74 mwestra74 is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Oct 2017
Posts: 5
mwestra74 is on a distinguished road
Default

Quote:
Originally Posted by Debaser View Post
I'd assume the table is growing. Given that you have a table, you might as well use table references. The #N/A means there aren't any values meeting the criteria. If there should be, which seems to be the case if copying and pasting the same data produces results, then there must be a formatting difference.
Tabel in sharepoint is now counting 3039 rows. I wrote the formula with rows for 3 years (using B:B would be too much). Could I adjust formula to actual amount of rows (B$2:B$3039)? Since the formula grows it will adjust to the growing sharepoint.
Reply With Quote
  #9  
Old 10-06-2017, 05:35 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

Yes - you would use TableName[ColumnName] syntax.
Reply With Quote
  #10  
Old 10-06-2017, 05:37 AM
mwestra74 mwestra74 is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Oct 2017
Posts: 5
mwestra74 is on a distinguished road
Default

Quote:
Originally Posted by Debaser View Post
Yes - you would use TableName[ColumnName] syntax.
Thanks for now. I 'll try that after my weekend 😉
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I make a checkbox or dropdown item reference data in that specific section of the data array? dhare Excel Programming 2 02-24-2016 12:36 PM
Data connection from Excel to Sharepoint not working GraemeW Excel 0 01-16-2016 04:31 PM
MS Excel - SQL Data Query Connection to MS Project - Resource Displayed in Multiple lines not in one AKent Excel 3 08-26-2015 01:34 PM
Excel 2007 security alert - data connection message every time? michael.monaco Excel 0 12-20-2012 07:47 AM
How to count the frequency of data and also tally value from an array of Excel record KIM SOLIS Excel 5 09-07-2011 09:01 AM


All times are GMT -7. The time now is 09:35 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft