Excel 2010 SUMPRODUCT Returns 0 But Simple SUMIF Returns 738

[PWD]

Good afternoon, I'm just having an issue with SUMPRODUCT. I have various cells with SUMPRODUCT in them. They refer to another worksheet but now we've changed our data source it's no longer a question of refreshing the data for that worksheet but appending for the next week's data. Now the SUMPRODUCT refuses to pick up the relevant data.

I've tried playing around with the formatting - to no avail. I thought I'd try a simple SUMIF on a different worksheet

=SUMIF(F:F,'Weekly Activity Summary'!I5,A:A) returns 738

=SUMPRODUCT(--(F2:F64000='Weekly Activity Summary'!I5),--(A2:A64000)) = 0

Interestingly(?) I replaced (Ctrl + H) a 'working' value in Column F, which returned nothing and when I replaced it back again I got 0 this time. VERY frustrating. Any suggestions?

Many thanks,
D€$

 

[SkipVought]

D€$, you're using Excel version 2007+ are you not?

Then I'd strongly suggest making your table a Structured Table and then using ST notation in your SUMPRODUCT() function rather than A1 references. Chances are you've exceeded row 64,000 in your table.

ALSO,
[tt]
=SUMPRODUCT(--(F2:F64000='Weekly Activity Summary'!I5)[highlight #FCE94F]*[/highlight](A2:A64000))
[/tt]



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Hi Skip, the data is/was previously an ST. How would I be able to append rows from another workbook (that has been produced from Business Objects)?

The original SUMPRODUCT was

=SUMPRODUCT((F2:F64002='Weekly Activity Summary'!I5)*(A2:A64002))

I just changed it to the other way hoping to get a result. I only put 64,000 in to try to future-proof the formula as this table has less than 300 rows at present. It is an xlsm workbook so has a million rows anyway.

It just seems as if it refuses to recognise a match for any new data rows that I add to the table. If I manually change the value in column F it will deduct the amount in column A corresponding to that week's formula but won't add that amount to this week's formula.

Many thanks,
D€$

 

[SkipVought]

The NUMERICAL data you're pasting in is looking to Excel like TEXT I'd guess. I recommend some other method than COPY N PASTE.

Can you upload your wb?

Hey, Aug 12 15:33 I posted a reply in one of your threads on this very issue! You had 'DATES' that were NOT dates.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Hi Skip, this is an un-tampered-with version, so doesn't contain the formulae I had posted above - which were in the 'IP Admissions' worksheet.

I will be adding data to the various worksheets and would then expect worksheet 'Weekly Activity Summary' to reflect those changes. I've just tried adding an extra line to the 'IP Admissions' worksheet but this has had no effect.

Many thanks,
D€$

 

[SkipVought]

Your data in Financial Week is TEXT!, The last row values that YOU entered in Financial week is NUMERIC.

I renamed the Table in IP Admissions tQRY1 and modified the formula to
[tt]
=SUMPRODUCT((tQRY1[Financial Week]='Weekly Activity Summary'!I5)*(tQRY1[Spell]))
[/tt]

So then I changed the NumberFormat of Financial week to TEXT and reentered 6 in the last row, and the formula returned 3, since row 5 in the Weekly Summary is formatted TEXT..


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...and continuing on from the previous post...

You had the Financial Week data with a NumberFormt of NUMBER. That does absolutely nothing to the underlying data which was TEXT. Simply changing the NumberFormat of a cell/range does NOTHING AT ALL to the values that are in the sheet. NumberFormat is a DISPLAY feature. That's all. If your value is already a number, then the number can be displayed in a whole passel of possibilities. But NumberFormat cannot chage a number to text or text to a number.
faq68-5827
faq68-6659

But be advised: Excel can and does CHANGE DATA YOU ENTER. faq68-7375

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...and BTW, in your IP Admissions sheet, you are IMPORTING data via MS Query.

But if you ADD data manually, that data will be lost the next time you REFRESH the QT.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Hi Skip thanks for this. I thought I'd tried all the various combinations of data type. I'll revisit it and follow your steps.

Yes, in the 'old world' we were importing data from an MS Query but now we've scrapped our Data Warehouse and are obtaining our data through Business objects; what would be the best way to append this data to the table(s in each source worksheet)?

Many thanks,
D€$

 

[SkipVought]

Ah yes. Just before I retired that's what happened. Since the analysis and reporting features were so immature in BO, I made a practice of exporting .csv files and importing into Excel, often on a production automated schedule to generate periodic management reports. The

To answer your append question, I'd need to know if BO can supply data to simply refresh the entire range of interest, or whether you would need to incrementally build the data. Either way, I'd export/import as described above. In the latter instance, you would import into a separate table and copy the data as

[tBO[#Data]].Copy

...and append to your table.

the reason I use the EXPORT/IMPORT feature is that I have better control over getting the data into Excel without data corruption.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}