Retrieving and summing multiple search results

wf · December 19, 2022, 6:56pm

I’m fairly certain that what I need to do is straightforward, but I have not been able to locate the necessary functions to accomplish it, and I appreciate everyone’s help in advance.

Simplified, I have a spreadsheet with three columns: Code, Overlap, and Total.

Code is of the form G001, G002, … G160.
Overlap is a boolean, determined by whether there is a particular overlap between the previous and current row.
Total is a positive integer.

There can be anywhere from 1 to 15 entries for G001 through G160. The sheet is sorted, so that G001s are grouped (and ordered to ensure that Overlap is correctly calculated), then G002, etc.

On a different sheet, I have G001 through G160 in column A; the number matches the row (G001 is on row 1, G160 is on row 160). I would like to calculate the sum of all the “Totals” for each “Code”, substracting 1 per overlap=TRUE.

The answer would seem to involve VLOOKUP(), except that VLOOKUP() only returns a single match and not an array of matches. Since there is no way to do loops in functions, I cannot easily iterate VLOOKUP() searches using OFFSET() to shrink the array to be searched each time. Is there a way to retrieve all of the cell locations for a particular search term?

Thank you!

karolus · December 19, 2022, 7:38pm

Hallo
try:

=SUMIF( Code; A1 ; -( Overlap ) + Total )

replace Code, Overlap and Total by their respective CellRange-address,
and finish the formula with <ctrl><shift><enter> OR with the [x]matrix-option in Formulawizard.
Pull down the Formula with [ctrl]-key pressed

wf · December 19, 2022, 8:30pm

Thank you, SUMIF is exactly what I was looking for!

However, I was not able to do -(Overlap)+Total in one step. When I tried I got a #VALUE error. I had to calculate those in separate columns and then combine the results in a third column.

I also didn’t seem to need to set up the function as an array. Once I entered the requisite ranges, I was able to just copy and paste the formula down the whole column normally.

Thank you again for your help!

Lupp · December 19, 2022, 9:55pm

Array evaluation doesn’t necessarily mean that the output goes to an array. It can well be a single cell. If the parameters of the used function need - in a specific use-case - “iterative” treatment , but are not specified ForceArray anyway, you need to force array-evaluation explicitly (CSE grip).
Since the parameters of SUMIF() and SUMIFS() not are specified ForceArray, the need for ESC should apply to the given example.
The first parameter of the mentioned functions is a “very special case”. If interested in more detail, you should read about these functions in the relevant document: “OpenDocument-v1.3-cs01-part4-formula” available as .odt or pdf.

Make sure the used ranges are absolute.

wf · December 20, 2022, 7:23pm

I think I only understand a portion of what you wrote, and perhaps even that I’m not understanding. What I think you’re saying is that when SUMIF() takes a range as its first parameter, it needs to be told explicitly to treat that range as an array. From there on I’m lost:

What is “CSE grip”, and how does it relate to ESC that you wrote in the third line?
If SUMIF() needs to be told to engage in array evaluation, why is it working for me without being told to do so?
It seems strange to me that SUMIF() would not automatically treat the range fed to it as an array, since it seems that whole point of the function is to work iteratively. (Perhaps that’s dealt with in the document you reference, which I am unable to check at this time.)
I am using absolutes – I had to in order to copy down the function, which otherwise was incrementing my references. Is this related to the array evaluation stuff, or a separate point?

Thank you for clarifying.

Lupp · December 20, 2022, 8:21pm

I didn’t want to say that. The “very special case” is a kind of parameter which will accept a reference to a CellRange, but it is also accepting a ReferenceList created with the range opperator ~ . I suppose this is very rarely used.
What I wanted to point out is that array evaluation can be needed to get a single value. An example: If the cells of A1:A5 and B1:B5 all contain numbers, the formula =SUM(A1:A5*B1:B5) will return an error because the one used parameter position doesn’t contain a reference or a number.
The same formula entered with CSE and then showing as {=SUM(A1:A5*B1:B5)} will return the same result as =SUMPRODUCT(A1:A5; B1:B5) where multiplication is implicit:
=SUMPRODUCT(A1:A5 * B1:B5) will again return the same result without CSE based on explicit multiplication because the parameter is specified ForceArray, and the elements of its ranges are therefore multiplied iteratively before the addition takes place.

I obviously expressed myself badly, and the exampes were misleading. Forget it! Read the mentioned document if you want to understand the details.

Villeroy · December 20, 2022, 9:06pm

this would be so if you were using a database instead of a spreadsheet. 90% of todays spreadsheet users try to misuse a spreadsheet as a database. However, all the database functionality has been added to the arithmetic core functionality. A spreadsheet has no concept of tables, fields and records. It is far away from being a database.
The single, outstanding feature which comes closest to database functionality is the pivot table. If your sheet data are normalized like a database table, a pivot table gets all the sums, counts, minimums, maximums, averages for any kind of category, item, person, day, week, month, year without writing a single formula. in database terms this is called an “aggregation query”. Unlike a spreadsheet formula, the aggregation derives a whole table from another table.

https://ask.libreoffice.org/uploads/short-url/m4CfCqn8ejQTSnyjsRjrJ48LYir.ods

OpenOffice documentation for pivot tables (aka data pilot) starts with:

Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.