#1




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 
#2




In your Excel version, you can use SUMIFS (NB! SUMIFS, not SUMIF) function instead of array formula  try this.

#3




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.

#4




@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 
#5




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.

#6




Quote:
Any idea this happens? 
#7




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.

#8




Quote:

#9




Yes  you would use TableName[ColumnName] syntax.

#10




Thanks for now. I 'll try that after my weekend 😉

#11




Worked like a charm

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  02242016 12:36 PM 
Data connection from Excel to Sharepoint not working  GraemeW  Excel  0  01162016 04:31 PM 
MS Excel  SQL Data Query Connection to MS Project  Resource Displayed in Multiple lines not in one  AKent  Excel  3  08262015 01:34 PM 
Excel 2007 security alert  data connection message every time?  michael.monaco  Excel  0  12202012 07:47 AM 
How to count the frequency of data and also tally value from an array of Excel record  KIM SOLIS  Excel  5  09072011 09:01 AM 