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