Help with SUMIF with OFFSET by MATCH amount

[Bald Eagle]

Hi folks,
I have location data and quantity data in sets of 10 columns, and I have two of these - one incoming, one outgoing.
If I don't select a location, I get the full list of all the data.
I'm trying to summarize the quantities of one location.

I'm getting an
Error:Value not available

I'd also like to know if there's some way to format the formulas in a cell, like with [CTRL][Enter], but that will be retained after hitting [Enter].
It sure helps a LOT to be able to indent and break up a complex formula into separate lines.

Any ideas?

=IF(ISBLANK($A$3);
IF(SUM(R8:AA8)=0;
"";
SUM(R8:AA8)
);

IF($E8=$'2018 Inventory'.$A20;
SUMIF(
OFFSET($'2018 Inventory'.$O19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$X19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0);
$A$3;
OFFSET($'2018 Inventory'.$E19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$N19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0)
)
-
SUMIF(
OFFSET($'2018 Inventory'.$AS19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$BB19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0);
$A$3;
OFFSET($'2018 Inventory'.$AI19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$AR19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0)
)
;0
)
)

[Zizi64]

Please upload your ODF type sample file here.

[keme]

[...]
It sure helps a LOT to be able to indent and break up a complex formula into separate lines.
[...]

Yes it does. It helps even more if you use the CODE tag, so the intents indents show in your post
("intent" may have been a Freudian slip, or perhaps autocorrect... )

Code: Select all

=IF(ISBLANK($A$3);
	IF(SUM(R8:AA8)=0;
		"";
		SUM(R8:AA8)
	); 

	IF($E8=$'2018 Inventory'.$A20; 
		SUMIF( 
			OFFSET($'2018 Inventory'.$O19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0): 
			OFFSET($'2018 Inventory'.$X19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0); 
			$A$3; 
			OFFSET($'2018 Inventory'.$E19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0): 
			OFFSET($'2018 Inventory'.$N19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0)
		) 
		- 
		SUMIF( 
			OFFSET($'2018 Inventory'.$AS19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0): 
			OFFSET($'2018 Inventory'.$BB19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0); 
			$A$3; 
			OFFSET($'2018 Inventory'.$AI19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0): 
			OFFSET($'2018 Inventory'.$AR19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0)
		) 
		;0
	) 
)

[Bald Eagle]

Whoops. Sorry. Too many fora.

Anyway, sorry for the delay - had to trim it down and clean it up a bit.
Still not entirely sure where the error is - maybe absolute vs relative cell referencing? Too much going on all at once - but I'm trying to do it this way so I don't have 40 helper columns...

It's probably hard to follow without knowing the evolution.

[Bald Eagle]

The Master sheet obviously has all the model numbers and the descriptions, and all the other related info.
The inventory sheet has a column for part numbers, and does a lookup for the descriptions.
Then there are columns for incoming quantities, where they're stored, and the date. Same for outgoing items.

The "By Type" is where it gets interesting.
The user inputs a number into cell A4 which describes what type of item they want to compile an inventory sublist for.
That acts as an offset.
a8:a28 check A2 against the offset cell in the master sheet, and if it matches, it returns an incremented number, so essentially you get a list of all matching items numbered 1 through N.
Columns B through D just look up the associated info for those items.
Column P in the master sheet is just a numerical list used as a reference.
Column E in "By Type" uses that reference to consolidate the matching items into a contiguous list, and Columns F&G are the associated info.

Columns H-Q return the inventory locations for the matching subitems, and R-AA the quantities.

What I'm trying to do is look at the inventory sheet, and if there's a specific location specified in A3, do a SUMIF on the quantities matching that location, SUMIF the quantities subtracted that location, and return a difference to give what should be the total current count.

[MrProgrammer]

I'd also like to know if there's some way to format the formulas in a cell, like with [CTRL][Enter], but that will be retained after hitting [Enter].

As far as I know, there is not. As a result, I find that long formulas are difficult to use.

I'm getting an Error:Value not available. Any ideas?

#N/A either is a result of referencing a cell with that value, or a function result. From a look at your formula, the only function which can return #N/A is MATCH. Check that. Otherwise check the formula's precedent cells for #N/A and repeat the process until you find the difficulty.

I'm trying to do it this way so I don't have 40 helper columns

If you want to write monster formulas, you' should learn how to use F9 and the Insert → Function facilities as descrined in Help → Index → formulas;inputting and function list window). (Insert → Function on a cell with a formula helps you analyze it.) They both allow you to determine the result of subexpressions. Many, perhaps most, spreadsheet experts have learned that it is much better to use helper columns instead of monster formulas. You will solve errors like this more easily when you use multiple cells with simple formulas.

=IF(ISBLANK($A$3);
IF(SUM(R8:AA8)=0;
"";
SUM(R8:AA8)
);

IF($E8=$'2018 Inventory'.$A20;
SUMIF(
OFFSET($'2018 Inventory'.$O19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$X19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0);
$A$3;
OFFSET($'2018 Inventory'.$E19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$N19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0)
)
-
SUMIF(
OFFSET($'2018 Inventory'.$AS19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$BB19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0);
$A$3;
OFFSET($'2018 Inventory'.$AI19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0):
OFFSET($'2018 Inventory'.$AR19;MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE());0)
)
;0
)
)

If you put MATCH($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$A$3000;FALSE()) in a separate cell you can evaluate it once and reference it eight times. That one change would shorten your formula considerably. Not that you should concern yourself much with efficiency, but I hope you realize that your monster formula has to perform eight separate evaluations of MATCH(…). Don't expect the spreadsheet to optimize the calculation by detecting common subexpressions.

With that MATCH(…) in ZZ8 your first 7-line SUMIF becomes:
SUMIF(OFFSET($'2018 Inventory'.$O19;$ZZ8;0,1,10);$A$3;OFFSET($'2018 Inventory'.$E19;$ZZ8;0,1,10))

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Bald Eagle]

Hmmm.
Thanks MrProgrammer for the tips


I do agree that calculating once and referencing 8 times is much more efficient. I hadn't gotten there yet.
However, your
"With that MATCH(…) in ZZ8 your first 7-line SUMIF becomes:
SUMIF(OFFSET($'2018 Inventory'.$O19;$ZZ8;0,1,10);$A$3;OFFSET($'2018 Inventory'.$E19;$ZZ8;0,1,10))"

formula returns a result (once the commas are replaced with semicolons)

But it seems to be missing some terms.
the idea is that I have a range of 10 cells that have locations.
I use that range of 10 cells, and if any of them equal the location in A3, then the corresponding cells in the second range get summed - If I understand the operation of SUMIF() correctly.

Now what I want to do is use the result of MATCH() to offset those 2 ranges in the inventory sheet.


So let's say that I do:
=SUMIF(OFFSET($'2018 Inventory'.$O19;$AH8;0) : OFFSET($'2018 Inventory'.$X19;$AH8;0) ;$A$3; OFFSET($'2018 Inventory'.$E19;$AH8;0) : OFFSET($'2018 Inventory'.$N19;$AH8;0) )

When I look for a specific model number, I get 929 as a result from MATCH(). That means that 929 rows down in my MATCH() array, I found the model number.
That array starts on row 20, so if I use the lowest possible MATCH() result of 1, I'm offsetting my SUMIF() arrays from row 19. And indeed the inventory information is in row 19+929 = 948.

In an earlier iteration of what I'm trying to do, I used
=IF(ISNA(VLOOKUP($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$D$3261;1;0)); ""; VLOOKUP($E8;$'2018 Inventory'.$A$20:$'2018 Inventory'.$N$3261;COLUMN(R8)-13;0))
in 10 rows, and I get a count of 2. My sumif formula currently returns zero.

Writing this out, I think maybe the problem is OFFSET() is returning cell contents, not cell addresses, which is what I need for SUMIF().

[Bald Eagle]

Consider this (which doesn't work):

=SUMIF(ADDRESS(AH8+19;15;3;;A$6) : ADDRESS(AH8+19;24;3;;A$6) ;$A$3; ADDRESS(AH8+19;5;3;;A$6) : ADDRESS(AH8+19;14;3;;A$6) )


I want to conditionally evaluate columns O-X, and sum columns E-N, with the row determined by MATCH() which is in column AH

[Bald Eagle]

So, I kept working on this to try to figure out how to code the two variable ranges I need for SUMIF()

I tried putting the formulas for the cell range in quotes, using only one page reference then a colon then a plain cell reference, something =cell("ADDRESS";ADDRESS(AL8+19;24;4;$A$6)), all with no success.
Apparently OpenOffice doesn't like something about the way it internally returns the text format of the cell addresses.

So I used FOUR helper columns with formulas like:
="'"&$A$6&"'."&ADDRESS(AL8+19;15;4)&":"&ADDRESS(AL8+19;24;4)
in them, and then used
=SUMIF(INDIRECT(AC8) ;$A$3; INDIRECT(AD8) ) - SUMIF(INDIRECT(AE8) ;$A$3; INDIRECT(AF8) )
to test the incoming quantities against the location and the outgoing quantities against the location, and subtract one from the other.

and that's what I have so far.

[keme]

I am getting the picture, slowly... Still a little confused, though.

In your spreadsheet I cannot find anything like the formula you outlined in your first post. Where do you want your formula to go? Is it a single calculation, or do you need one per row in a table?

[Bald Eagle]

I am getting the picture, slowly... Still a little confused, though.

In your spreadsheet I cannot find anything like the formula you outlined in your first post. Where do you want your formula to go? Is it a single calculation, or do you need one per row in a table?

I believe it's in sheet "By Type", column AB

I'd need one per row, as the SUMIF() would be done for each model number.

I'll try to put together a small sample of what I have now - and it _IS_ complicated --- sometimes it's hard for ME to follow, and I'm the one writing it.

A few other questions, which I may open other threads for:
It would be helpful to have a way to see what a cell's RESULT is, if the cell is small and in a column between other cells with content.
IIRC, EXCEL had some sort of [F9] function that evaluated the current expression in the formula bar --- any way that OpenOffice can do something like this?

Also, I'm having some trouble understanding how OpenOffice interprets the conditional statement for SUMIF(). "=$A$3" didn't work, but just $A$3 does.
I'm interested in writing an expression for summing if the cell contents equal a specific value, but ALSO if another cell has "All"

So, (ignoring the heinous pseudo syntax:)
SUMIF ( TheseCells; EqualThis OR "CellA3=All"; SumTheseCells)

[Villeroy]

SUMIF and F9 work the same in Excel and in Calc.
F9 recalculates the document when automatic recalculation is disabled and it recalculates all volatile functions such as RAND(), NOW(), TODAY() and others.
WHile in edit mode, F9 calculates the current formula, shows the result in a tool tip and when you hit enter, the cell gets the formula result as constant value.
Ctrl+Shift+F9 enforces the recalculation of the whole document.

[Bald Eagle]

Thanks, Villeroy - Somehow that was stuck in the chair-keyboard interface
"Just PUSH [F9] AND SEE WHAT HAPPENS!"

I actually JUST got done looking up and learning the [Ctrl][Shift][F9] key combo for another purpose {updating CELL("filename") }

Here you go, Keme - I copied over some cells from the working sheets so you can get the idea.

I think I busted some of the other formulas on the 2018 Inventory sheet, but that's not germane to the SUMIF() problem at hand.
Basically what I was/am trying to do is what's in sheet "by TYPE" column AG but without the 4 helper columns to the left of that.

I could still use a few examples of the comparison argument in SUMIF() to better understand what the range of complexity can be.

Thanks!

[Villeroy]

I actually JUST got done looking up and learning the [Ctrl][Shift][F9] key combo for another purpose {updating CELL("filename") }

Same as in Excel.
Cell formulas recalculate when their input values change.
=CELL(A1) recalculates automatically or on F9 key press when A1 has changed.
=CELL("filename") never recalculates because "filename" is a constant. Of course you expect to recalculate CELL("filename") when the file location changed but there is no extra rule for this particular case.

There is one excheption to the rule for volatile functions which change over time. RAND() and NOW() recalculate every time when anything changes or when hitting F9.

[keme]

The product IDs in the "Master" sheet are dynamic, and randomly generated. The Part# entries in "2018 inventory" are static. The #NA error spawns from trying to look up a product ID (random, fetched from the Master sheet) in the Part# field of the inventory list.

With the current randomizing, you have around 0,0031% probability of a recalc yielding one match (i.e. a single line without #NA) in your 21 lines. Don't hold your breath in anticipation...

I suspect that the static list was at one time copied from those random codes. Next time the Master sheet was recalculated, new codes were generated and the old IDs didn't match.

[Bald Eagle]

Yes, the data there doesn't matter. It's the processing of it that does.
The last spreadsheet has good static data,
and what I was/am trying to do is what's in sheet "by TYPE" column AG but without the 4 helper columns to the left of that.

And I could STILL use a few examples of the comparison argument in SUMIF() to better understand what the range of complexity can be.
I've tried to do something like sum 2 cells if their individual values are > 10 OR if another cell = TRUE, but it didn't work - I got 0 as a result.