Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-03-2017, 03:26 PM
mwestra74 mwestra74 is offline Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2013
Novice
Data connection problem excel/sharepoint using an SUMIF array
 
Join Date: Oct 2017
Posts: 7
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 Data connection problem excel/sharepoint using an SUMIF array Windows 8 Data connection problem excel/sharepoint using an SUMIF array Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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's Avatar
Debaser Debaser is offline Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
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 Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2013
Novice
Data connection problem excel/sharepoint using an SUMIF array
 
Join Date: Oct 2017
Posts: 7
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's Avatar
Debaser Debaser is offline Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
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 Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2013
Novice
Data connection problem excel/sharepoint using an SUMIF array
 
Join Date: Oct 2017
Posts: 7
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's Avatar
Debaser Debaser is offline Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
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 Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2013
Novice
Data connection problem excel/sharepoint using an SUMIF array
 
Join Date: Oct 2017
Posts: 7
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's Avatar
Debaser Debaser is offline Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

Yes - you would use TableName[ColumnName] syntax.
Reply With Quote
  #10  
Old 10-06-2017, 05:37 AM
mwestra74 mwestra74 is offline Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2013
Novice
Data connection problem excel/sharepoint using an SUMIF array
 
Join Date: Oct 2017
Posts: 7
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
  #11  
Old 11-28-2017, 06:26 PM
mwestra74 mwestra74 is offline Data connection problem excel/sharepoint using an SUMIF array Windows 7 64bit Data connection problem excel/sharepoint using an SUMIF array Office 2013
Novice
Data connection problem excel/sharepoint using an SUMIF array
 
Join Date: Oct 2017
Posts: 7
mwestra74 is on a distinguished road
Default

Worked like a charm
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data connection problem excel/sharepoint using an SUMIF array 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft