Ask Your Question
0

Convert Excel multi function formula to Calc [closed]

asked 2014-10-23 22:17:20 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$15&"'!$A$2:$A$33"),$A19,INDIRECT("'"&$A$1:$A$15&"'!D$2:D$33")))

The formula works in Excel but in Calc it's giving sporadic results - which indicates to me the syntax isn't completely incorrect.

I have the profile formula set to Excel A1.

Cells A1:A15 contain the names of 15 different sheets I'm wanting to search and total the amounts. Cell A19 contains the value to search each sheet. The values being search are in A2:A33 on each sheet and the amounts being added in each sheet are in D2:D33.

I'm not getting any errors and I haven't been able to determine a pattern in the results but some results are coming from different sheets but none are totaling from multiple sheets. I initially thought there could be an issue with the cells being different formats so it wasn't finding the values but all appear to be the same.

Any suggestions would be appreciated.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 12:03:08.915897

Comments

Could it be the ! (exclamation mark)? LO uses a . (period/dot) to separate the sheet name from the cells. The formula may not convert properly when xls is loaded into LO?

Rugslug gravatar imageRugslug ( 2014-10-24 01:07:47 +0200 )edit

Yes, LO uses a dot whereas Google Sheets uses §!§. The format is §'SHEET_NAME'.COLUMN#§ where §SHEET_NAME§ is the target sheet's name in between apostrophes, then a dot, the column letter and then the row number. Google Sheets uses the exactly same but not a dot.

rautamiekka gravatar imagerautamiekka ( 2014-10-24 01:15:41 +0200 )edit

NewerCalcUser gravatar image NewerCalcUser 1 ●1

First thanks for the response. Tried replacing the exclamation mark (!) with a period (.) it didn't give an error but gave no results.

NewerCalcUser gravatar imageNewerCalcUser ( 2014-10-24 03:26:32 +0200 )edit

I've manipulated the formula many ways replacing the "!" with "." and placing a "$" in front of the sheet names. The issues seems to be around the first range (A1:A15) giving the sheet names. If I replace that range with a single value - lets say A1 in both places, I get the correct results for the one sheet as long as the value I'm searching on is on the sheet.

NewerCalcUser gravatar imageNewerCalcUser ( 2014-10-25 18:40:22 +0200 )edit

Reported bug as recommended below #85539

NewerCalcUser gravatar imageNewerCalcUser ( 2014-10-29 01:38:08 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2014-10-25 08:16:25 +0200

ROSt52 gravatar image

Unfortunately I don' t have MSO anymore to make a test.

What makes me wondering is @NewerCalcUser 's statement "it's giving sporadic results". If there are "sporatic" results there must be different conditions for the results and the cases without results. Whenever I has sporadic results in Excel or in Calc, I also had an error in one of the cells delivering information to the cell with the formula which did not show a correct result.

I would look into a result case and a non result case in ALL cells contributing to the cell containing "=SUMPRODUCT(SUMIF(INDIR....."- formula for differences in formats and especially if all contributing cells are populated. I also would use the elements of the "Detective" toolbar.

edit flag offensive delete link more

Comments

Since I'm not as familiar with Calc, I will have to look into the "Detective" toolbar. Maybe my use of sporadic wasn't the best choice of words. Out of 40 rows looking to get results, 6 would produce results. I still believe the issue has to do with being able to reference a range that has all the sheet names and getting the formula in this manner.

NewerCalcUser gravatar imageNewerCalcUser ( 2014-10-25 18:52:58 +0200 )edit
0

answered 2014-10-25 19:30:25 +0200

m.a.riosv gravatar image

I think their is a bug, take a look at the example file. Seems only one sheet is summed.

Sumproduct_test_multisheet

Please could you report the bug.

Bug Submission Assistant

If you want attach sample file to the report.

edit flag offensive delete link more

Comments

Unfortunately I could not retrieve and view your file. I created another test file and I am only getting results from the last sheet in the range but for only one row. If I replace the range with a single cell reference it sums the entire sheet correctly - so it works for one cell reference but not a range.

There doesn't seem to be a way to post my example file - I don't have an account set up with the Bug Submission Assistant but may look into setting up to report.

NewerCalcUser gravatar imageNewerCalcUser ( 2014-10-28 02:54:58 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-10-23 22:17:20 +0200

Seen: 738 times

Last updated: Oct 25 '14