Largest Absolute Value in Excel with MAX and XLOOKUP

The problem is finding the biggest numerical value by magnitude across four cells, while keeping the original sign. This comes up when an operator needs to identify the largest gain or loss, inventory adjustment, price variation, or other extreme number in Excel.

The problem

A standard MAX formula returns the largest value based on normal number order. For example, between -900 and 700, MAX returns 700, even though -900 has the greater absolute value. The ABS function solves the comparison by removing the sign, but that creates a second problem: the result no longer shows whether the original number was positive or negative.

This issue came up in the r/excel community, where the goal was to compare four cells and return the value with the greatest absolute value while preserving its sign. The original discussion is available in the r/excel question about largest values with positive and negative signs.

The key distinction is:

• Largest value: normal numeric order, so 700 is greater than -900.
• Largest absolute value: distance from zero, so -900 is greater in magnitude than 700.
• Preserved sign: the returned answer remains -900, not 900.

The solution

For four separate cells, use this formula:

=IF(MAX(A1,B1,C1,D1)>=-MIN(A1,B1,C1,D1),MAX(A1,B1,C1,D1),MIN(A1,B1,C1,D1))

The formula compares the largest positive-side value with the magnitude of the smallest negative-side value.

• MAX(A1,B1,C1,D1) returns the highest ordinary value.
• MIN(A1,B1,C1,D1) returns the lowest value, usually the most negative one.
• -MIN(A1,B1,C1,D1) converts that negative value into its positive magnitude for comparison.
• IF checks which magnitude is larger.
• The formula returns the original MAX or MIN result, so the sign remains intact.

For example, suppose the four cells contain:

Value 1	Value 2	Value 3	Value 4	Result
125	-480	210	-95	-480

The formula compares 210 with 480, then returns the original -480.

This method is robust when the inputs are four distinct cells or references. It also handles cases where all values are positive or all values are negative.

When the values are already in one contiguous cell range, a more compact approach is possible with dynamic array functions available in newer Excel versions:

=LET(x,A1:A4,XLOOKUP(MAX(ABS(x)),ABS(x),x))

LET assigns the range to the name x, which makes the formula easier to read. ABS(x) calculates each value’s absolute value. MAX(ABS(x)) finds the greatest magnitude. XLOOKUP then searches for that magnitude and returns the corresponding original value from x, including its sign.

There is one tie to understand. If the range contains 500 and -500, the lookup returns the first matching value in the range. If your business rule must always prefer the positive or negative value in a tie, add that rule explicitly instead of relying on position.

The same logic can also sort the values by magnitude:

=SORTBY(A1:A4,ABS(A1:A4),-1)

SORTBY returns the full range ordered from greatest absolute value to smallest. The -1 means descending order. This is useful when you need the top three unusual transactions, not only the single biggest one.

How to apply it

1. Confirm that the cells contain real numbers, not numbers stored as text. A value that looks like -480 but is text may not behave correctly in MAX or MIN.

2. Decide whether the cells are separate or contiguous. Use the IF, MAX, and MIN formula for separate cells such as B4, E4, H4, and K4. Use the LET and XLOOKUP formula for a range such as B4:B7.

3. Enter the formula in the result cell. For separate cells, replace A1, B1, C1, and D1 with the actual references.

4. Test the formula with one clearly dominant positive value and one clearly dominant negative value. For example, test 300 against -700 and confirm that the result is -700.

5. Test a tie, such as 700 and -700. Decide whether the first value, the positive value, or the negative value should win.

6. If you need a ranked list rather than one answer, use SORTBY. Keep the original values in the returned array so the signs remain visible.

7. Add error handling if the input cells may contain blanks, labels, or invalid values. For example, IFERROR can return a controlled message instead of exposing an Excel error, but the exact handling should match your reporting process.

For a monthly sales report, this pattern can identify the largest positive or negative variance. For inventory, it can identify the biggest adjustment. For quotes, it can show the most extreme price change while preserving whether the change increased or decreased the amount.

A better data structure may be preferable when the workbook grows. Instead of storing four related values across unrelated cells, place them in one vertical table with columns such as date, product, transaction type, and amount. A single cell range or Excel Table is easier to filter, sort, audit, and connect to automation.

Implementation angle

This formula can become a controlled exception check in a sales, inventory, or finance workbook. An automation partner can move the source records into Zoho Creator, use Make or n8n to flag transactions whose absolute variance exceeds a threshold, and send the original signed value to the responsible person. Claude can help build and check the formula against positive, negative, zero, and tie cases before it reaches a live report.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

How do I find the largest absolute value in Excel and keep its sign?

For four cells, use =IF(MAX(A1,B1,C1,D1)>=-MIN(A1,B1,C1,D1),MAX(A1,B1,C1,D1),MIN(A1,B1,C1,D1)). It compares the positive and negative magnitudes, then returns the original value with its sign.

How can I find the largest value by magnitude in a range in Excel?

In newer Excel versions, use =LET(x,A1:A4,XLOOKUP(MAX(ABS(x)),ABS(x),x)). Replace A1:A4 with your range. The formula returns the original value, including its sign.

What happens if positive and negative values tie in absolute value?

With the XLOOKUP formula, Excel returns the first matching value in the range. If a positive or negative value must always win a tie, set that rule explicitly in your formula.

Excel Latitude and Longitude with Geography Data Type

Cities stored in Column A can provide their Latitude and Longitude automatically when Excel recognizes them as Geography data types. This works well for operators who need coordinates for delivery zones, branch maps, service areas, or location-based reports.

The problem

A city name in Column A is only text until Excel converts it into a linked Geography data type. A linked data type connects the cell to online geographic information and displays a small map icon beside the city. Excel can then retrieve fields such as Latitude and Longitude from that city record. Microsoft documents this workflow for city, country, state, and province names.[4][5]

This issue came up in the r/excel community, where the goal was to place each city’s Latitude in Column B and Longitude in Column C automatically. The original Excel community question shows the map icon already appearing beside the city names.

The solution

First convert the cities in Column A to the Geography data type:

1. Select the city names in Column A.
2. Open the Data tab.
3. Choose Geography from the Data Types section.
4. Check that Excel adds a map icon beside each recognized city.
5. Resolve any ambiguous matches by selecting the cell and choosing the correct location from the card or suggestion menu.

Once Excel recognizes the cities, use these formulas:

• In B2, enter =A2.Latitude
• In C2, enter =A2.Longitude

The dot after A2 tells Excel to retrieve a field from the linked geography record. Latitude returns the north or south position. Longitude returns the east or west position. Excel supports this dot notation for formulas that reference Geography data types.[2][4]

Copy both formulas down for the remaining rows. Excel will adjust A2 to A3, A4, and so on.

This is usually the clearest approach because each output column has one purpose. Column B contains Latitude. Column C contains Longitude. Operators can inspect or adjust either field without changing the other.

A second approach can return both fields with one dynamic-array formula. A dynamic array is a formula result that automatically fills neighboring cells. Enter this in B2:

=FIELDVALUE(DROP(A:A,1),{"Latitude","Longitude"})

FIELDVALUE retrieves one or more fields from a linked data type. DROP(A:A,1) removes the first row of Column A, which is useful when row 1 contains headers. The array {"Latitude","Longitude"} requests the two fields in that order. Microsoft defines the FIELDVALUE syntax as FIELDVALUE(value, field_name) and the DROP syntax as DROP(array, rows, [columns]).[1][3]

The combined formula can populate two columns and multiple rows from one cell. However, the separate formulas are more robust for most business workbooks. They are easier to troubleshoot, easier to explain to staff, and less likely to fail because another value is occupying the spill area.

How to apply it

1. Put one city name per row in Column A. For reliable matching, include a state, province, or country when a name is common. For example, use Puebla, Mexico instead of only Puebla when the workbook includes locations from multiple countries.
2. Select the city range, such as A2:A100.
3. Go to Data, then choose Geography under Data Types.
4. Confirm that the map icon appears beside each recognized city.
5. Click a cell with an uncertain match and select the correct location from Excel’s suggestions.
6. Add Latitude as the header in B1 and Longitude as the header in C1.
7. Enter =A2.Latitude in B2.
8. Enter =A2.Longitude in C2.
9. Fill both formulas down through the list.
10. Check several results against the city cards. This catches spelling errors and cities with the same name.
11. Convert the range into an Excel table with Insert, Table if the list will grow. Tables make it easier to keep formulas and headers aligned as new cities are added. Microsoft recommends creating a table when working with geographic data, although it is not mandatory.[8]
12. If a formula returns an error, inspect the source cell in Column A. The city may still be plain text, the location may be ambiguous, or the required field may not be available for that record.

Do not assume this is the same as Google Sheets. Excel’s map icon and dot formulas depend on Excel’s linked data type feature. A workbook opened in another spreadsheet application may not preserve the same behavior.

Keep a practical fallback for important operational files. If coordinates drive route planning, delivery pricing, or customer commitments, store a reviewed copy of the coordinates as values after validation. Linked data types use an online data source, so a business process should not depend on an unverified match.

Implementation angle

This pattern can become a controlled location-enrichment step for a sales, delivery, or inventory workbook. An automation partner could move the city list into Zoho Creator, use Make or n8n to validate locations and synchronize approved coordinates, then return a clean table for Excel reporting. Claude can also help generate the Latitude and Longitude formulas, check for ambiguous city names, and flag rows that did not populate correctly.

Book an AI implementation engagement with Creator Scripts to turn this location process into a reliable workflow.

Frequently asked questions

How do I get latitude and longitude from a city name in Excel?

Convert the city cells to the Geography data type from the Data tab. Then enter =A2.Latitude and =A2.Longitude in separate columns, replacing A2 with the cell that contains the city. Fill the formulas down the list.

Why is my Excel latitude or longitude formula returning an error?

Check that Excel converted the city name to the Geography data type and shows a map icon in the cell. If the city name is unclear, add a state, province, or country and select the correct match. The requested field may also be unavailable for that location.

Can Excel fill latitude and longitude for a whole list of cities?

Yes. Enter the latitude and longitude formulas beside the first city, then fill them down for the other rows. You can also use a dynamic-array formula, but the cells where results should appear must be empty.

Excel FILTER Function for Dependent Drop-Down Lists

A drop-down list can show the vegetables related to a selected category, but the current layout makes the task harder than it needs to be. The most reliable fix is to separate the data from its visual presentation, remove merged cells from the working range, and then use either a PivotTable or a Microsoft 365 dynamic array formula.

The problem

This problem came up in the r/excel community discussion. The worksheet uses a yellow category heading and a veggie category heading merged across several columns. The goal is to choose a vegetable from a drop-down menu and show only the corresponding data while hiding unrelated values.

The difficulty is not mainly the data validation drop-down. Data validation controls what a user can select. It does not automatically reshape a report or hide unrelated columns.

Merged cells are the larger issue. A merged heading may look clean, but it does not behave like a normal value repeated across each record. That creates problems when Excel tries to sort, filter, convert the range into a table, or use formulas that expect one row per item. Microsoft documents that Excel cannot sort a column containing merged cells.

The best structure is a normal data table with one record per row. Store the category in its own column, store the vegetable in another column, and keep the related measurements or attributes in additional columns. Use formatting on a separate report area if you want the worksheet to retain its visual design.

The solution

For a working file, the most robust approach is:

• Unmerge the category and vegetable headings in the source data.
• Repeat the category value on every applicable row.
• Convert the cleaned range into an Excel table.
• Use a drop-down cell for the selected category or vegetable.
• Display the matching records in a separate report area.

If the workbook uses Microsoft 365, a dynamic array formula can produce the filtered report automatically. A dynamic array is a formula that returns multiple cells from one formula.

Assume:

• B8:B19 contains a field such as product, code, or item.
• C8:J19 contains the data to display.
• Row 8 contains category labels that may be visually grouped.
• N6 contains the selected category.
• Blank cells in row 8 mean that the previous category continues across the columns.

A formula based on the community answer is:

=LET(_Output,HSTACK(B8:B19,FILTER(C8:J19,SCAN("",C8:J8,LAMBDA(x,y,IF(y="",x,y)))=N6,"")),_Header,TAKE(_Output,2),_Answer,VSTACK(IF(_Header=0,"",_Header),DROP(_Output,2)),_Answer)

LET (LET) assigns names to parts of the formula. _Output combines the item column with the filtered data. HSTACK places ranges side by side. SCAN carries the last nonblank category across the header row. LAMBDA defines the calculation used by SCAN. FILTER returns only the columns whose carried category matches N6. TAKE keeps the first two rows, and DROP removes them from the remaining data. VSTACK puts the cleaned header and filtered data back together.

This formula preserves the visual logic of the original layout without depending on merged cells. It also avoids OFFSET, a volatile function. Volatile functions recalculate whenever Excel recalculates the workbook, even when their direct inputs did not change. In a large file, excessive use can make the workbook slower.

A simpler alternative is to normalize the data first. Add a real Category column, then use a formula such as:

=FILTER(Table1,Table1[Category]=N6,"No matching records")

Table1[Category] refers to the Category column in the Excel table. The formula returns every row whose category matches the selection in N6. This approach is easier to maintain than a formula that reconstructs categories from merged headings.

A PivotTable is another strong option, especially when the user mainly needs to browse or summarize the information. PivotTables can filter fields, and slicers provide visible buttons for selecting categories. Microsoft explains that slicers can filter a PivotTable while keeping the available filter choices visible.

How to apply it

1. Make a backup copy of the workbook before changing the layout.

2. Identify the actual data range. Include the item name, category, vegetable, and every value that belongs to that record.

3. Unmerge the cells in the working range. Select the merged area, open the Merge menu, and choose to unmerge the cells.

4. Create a proper category column. If one yellow category covers several columns, enter that category in every relevant row or column record.

5. Avoid leaving category information only in formatting or merged headings. A formula cannot reliably use a color or visual grouping as business data.

6. Put each vegetable or product on its own row. Use columns such as Category, Vegetable, Unit, Cost, Stock, and Price.

7. Select the cleaned range and choose Insert, then Table. Confirm that the table has headers.

8. Create a selection cell, such as N6. Use Data Validation, choose List, and point it to a clean list of categories or vegetables.

9. If the table has a real Category column, place the simple FILTER formula in a separate report area. Replace Table1 and Category with the actual table and column names.

10. If the worksheet must retain its existing cross-column layout, use the LET, HSTACK, SCAN, FILTER, and VSTACK formula instead. Test it with several category selections.

11. If the report is mainly for review, select the normalized table and choose Insert, then PivotTable. Add the category or vegetable field as a filter, row field, or slicer.

12. Test blank categories, new vegetables, spelling differences, and empty results. Use a consistent naming convention, such as Tomato everywhere instead of mixing Tomato, tomatoes, and Tomate.

Center Across Selection can preserve a heading that visually spans multiple cells without merging them. It is useful for presentation, but it does not replace a real category column. Keep the source data normalized and apply presentation formatting only in the report area.

Implementation angle

For a small business, this structure can become a controlled inventory or quotation workflow. An automation partner could move the normalized vegetable data into Zoho Creator, connect stock and price updates through Make or n8n, and keep Excel as an export or reporting layer. Claude could help generate and check formulas, while validation rules would prevent inconsistent category and product names from entering the workbook.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

How do I make an Excel drop-down show only matching vegetables?

Keep the source data in a normal table with a category column and a vegetable column. Use a drop-down for the selected category, then use a FILTER formula in a separate report area to show matching rows.

Can I filter Excel data that has merged cells?

Merged cells can cause problems with sorting, filtering, and formulas. Unmerge them in the working data, repeat the category value on each applicable row, and use formatting in a separate report area.

What can I use instead of a FILTER formula in Excel?

A PivotTable can filter records by category or vegetable. Add a slicer if you want visible buttons for choosing a category.

Excel TEXT Function for Narrow Date Columns

The issue is not the visible number of characters alone. Excel calculates whether a formatted date fits using the cell’s display layout, including line breaks, font metrics, and the custom number format. With a multi-line date format such as mm/dd/yy ddd hh:mm AM/PM, Excel 365 can return ####### even when a visually similar single-line value appears to fit. This affects project trackers that need dates, weekdays, and times in narrow columns.

The problem

This came up in the r/excel community discussion about narrow date columns. The worksheet used a monospace font and tightly packed columns so a team could scan project due dates quickly. The goal was to show the full date, day of week, and time without giving the column extra width.

The custom number format was:

mm/dd/yy ddd hh:mm AM/PM

The same information displayed across multiple lines, but Excel treated the cell as too narrow at certain column widths. It replaced the value with #######, even though a single-line version containing spaces could fit in a narrower column.

Excel commonly displays hash characters when a number or date cannot fit in the available column width. Microsoft documents widening the column as the standard fix. That does not solve this particular layout problem because the worksheet has many columns and the available width is intentional.

The likely explanation is a limitation, or longstanding formatting bug, in how Excel measures custom-formatted dates that contain line breaks or carriage returns. Excel does not simply count visible characters. It measures the rendered result according to the cell’s formatting rules. A line break may reduce the visible width for a person while still producing an unexpected width calculation inside Excel.

The behavior has been reported in Excel 365 on Windows Desktop, including Version 2610, Build 20527.20088, Click-to-Run. It is safest to treat the custom multi-line number format as unreliable when every fraction of column width matters.

The solution

The most dependable workaround is to separate the stored date and time from the value shown to the user.

Keep the original date-time value in one column. Use a second display column with the TEXT function:

=TEXT(A2,"mm/dd/yy ddd hh:mm AM/PM")

Here, A2 contains the real Excel date and time. The text inside quotation marks tells Excel exactly how to display it. mm/dd/yy shows the date, ddd shows the abbreviated weekday, hh:mm shows the time, and AM/PM adds the time period.

Microsoft describes TEXT as a way to apply a format code to a number and return a more readable display value. The important tradeoff is that the result is text, not a numeric date-time. Sorting, filtering, calculating durations, and comparing deadlines should use the original date column, not the display column.

This design is more robust than trying to force Excel to interpret a multi-line custom number format correctly. It also makes the workbook easier to maintain. The data column remains suitable for formulas, while the display column is optimized for quick reading.

A second option is a macro that adjusts column widths or applies a preferred layout automatically. Excel’s VBA AutoFit method changes a column or range to fit its contents. However, that approach is less suitable here because the goal is to preserve a deliberately narrow worksheet. It can also behave differently when columns are grouped, so it should be tested before being used in a shared workbook.

The most practical recommendation is therefore:

• Store the true date-time value in a hidden or helper column.
• Display a text version in the narrow project-tracking column.
• Use the hidden value for calculations, sorting, and alerts.
• Avoid relying on the multi-line custom number format for critical dates.

How to apply it

1. Insert a helper column next to the visible project date column.
2. Keep the original date and time in the helper column. Confirm that Excel recognizes the value as a date-time by changing its format temporarily to General. A numeric value indicates that Excel is storing it correctly.
3. In the visible tracking column, enter:
   =TEXT(A2,"mm/dd/yy ddd hh:mm AM/PM")
   Replace A2 with the cell containing the original date-time.
4. Fill the formula down for all projects.
5. Apply the same monospace font used elsewhere in the worksheet. Because the output is now text, Excel will display the exact characters returned by the formula rather than trying to measure a complex multi-line number format.
6. Reduce the column width gradually until the text reaches the desired limit. If the complete value no longer fits, shorten the display format rather than hiding part of the deadline.
7. For a shorter version, use:
   =TEXT(A2,"mm/dd/yy ddd hh:mm")
   This removes the AM or PM suffix while preserving the date, weekday, and time.
8. Use the original date-time column for calculations. For example, to find the number of days between two project dates, subtract the stored values, not the TEXT results.
9. Sort the project list by the original date-time column. Sorting the display column can sort the values alphabetically because TEXT returns text.
10. Hide the helper column if the team only needs the compact view. Do not delete it, because it is the reliable source for formulas, filters, and future automation.
11. If the workbook uses formulas that refer to the old visible date column, update those formulas to point to the helper column.
12. Test the workbook at several widths, including the narrowest width used by the team. Check due dates, overnight times, blank cells, sorting, and filters before rolling the layout out.

If the team needs a genuinely multi-line visual, another option is to place separate values in separate rows or columns, such as date in one column and time in another. That changes the layout but avoids asking one cell to combine a complex date format with aggressive width constraints.

Implementation angle

A project tracker can use the stored date-time column as the system field while the compact TEXT column remains a presentation field. An automation partner could move the underlying project data into Zoho Creator, then sync due dates and status changes through Make or n8n while preserving the Excel view for quick team review. Claude can also help audit formulas and identify places where a text display value has accidentally been used in a calculation.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com to turn a fragile Excel project tracker into a reliable workflow.

Frequently asked questions

Why does Excel show hash marks for a date that looks like it should fit?

Excel measures the formatted cell, not just the characters you can see. Line breaks and custom date formats can make that measurement behave unexpectedly, especially in narrow columns.

How do I show a date and time in a narrow Excel column?

Keep the real date-time value in one column and use a second column with a formula such as =TEXT(A2,"mm/dd/yy ddd hh:mm AM/PM"). Adjust the format to be shorter if the full text does not fit.

Can I sort or calculate dates from a TEXT formula in Excel?

TEXT returns text, so sorting its results may be alphabetical rather than chronological. Use the original date-time values for sorting, filtering, comparisons, and calculations.

SUMIFS Returns 0 for Stock Out: Fix Merged Cells

The SUMIFS formula returns 0 for Stock Out because the item name is stored in merged cells. Excel keeps the value only in the top-left cell of a merged area. The rows below appear to show the same animal or item, but their cells are actually blank, so the formula cannot match them.

This problem commonly affects small-business workbooks used for inventory movements, where Stock In and Stock Out lines are grouped visually instead of stored as complete records.

The problem

This issue came up in the r/excel community discussion about SUMIFS returning 0 for Stock Out.

A SUMIFS formula performs a conditional sum. It checks one or more criteria ranges, finds rows that match the conditions, and adds the corresponding values from the sum range. Microsoft defines the structure as:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

The sum_range is the quantity column. Each criteria_range contains a field such as the item name, movement type, warehouse, or date.

In the yellow table, the first Stock In line works because it is the top-left cell of the merged item-name area. That cell contains the animal or item name. The following Stock Out lines look as though they contain the same name, but the underlying cells are blank.

For example, the sheet may look like this:

Item name	Movement	Quantity
Dog	Stock In	10
Dog	Stock Out	3
Dog	Stock Out	2

If the item-name cells are merged, Excel actually sees something closer to this:

Item name	Movement	Quantity
Dog	Stock In	10
blank	Stock Out	3
blank	Stock Out	2

The SUMIFS formula can find the first row because Dog exists there. It cannot find the Stock Out rows when it requires both Dog and Stock Out. No matching rows means the formula returns 0.

The issue is not that SUMIFS treats Stock Out differently. The condition exposes the missing item names in those rows.

The solution

The robust fix is to unmerge the item-name cells and repeat the item name on every transaction row. Do not use merged cells as a data-storage method. They are useful for presentation, but they make filtering, sorting, formulas, PivotTables, and automation unreliable.

After restructuring the data, the formula can use complete records:

=SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,"Stock Out")

Here is what each part means:

• $C$2:$C$100 is the quantity or sum range.
• $A$2:$A$100 is the item-name criteria range.
• E2 contains the item name to find.
• $B$2:$B$100 is the movement-type criteria range.
• "Stock Out" is the condition that must match.

For Stock In, change only the final criterion:

=SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,"Stock In")

The formula is correct only when the item name, movement type, and quantity are present in the same row. Microsoft also recommends that the criteria ranges and sum range use matching dimensions.

An alternative is to fill the blank item-name cells with the value from the row above, but this is still a manual repair. A flat table with one complete record per row is more dependable.

How to apply it

1. Make a copy of the workbook before changing the layout.

2. Select the merged item-name column or the relevant merged areas.

3. Use Excel’s Unmerge Cells command.

4. Identify the blank cells created below each original item name.

5. Fill each blank item-name cell with the item or animal from the row above. Every Stock In and Stock Out line must have its own item name.

6. Check the movement column. Use one consistent spelling for each condition, such as Stock In and Stock Out. Avoid extra spaces and inconsistent capitalization.

7. Check the quantity column. Quantities should be numeric values, not numbers stored as text.

8. Convert the range into an Excel Table with Insert, Table. Tables expand automatically as new inventory movements are added.

9. Rebuild the formula using the table columns. If the table is named Inventory, a readable version is:

   =SUMIFS(Inventory[Quantity],Inventory[Item],E2,Inventory[Movement],"Stock Out")

   Inventory[Quantity] is the column to add. Inventory[Item] identifies the animal or product. Inventory[Movement] contains Stock In or Stock Out. E2 is the selected item.

10. Test the result with one item that has both movement types. Add the Stock In quantities separately, then add the Stock Out quantities. Confirm that each total matches the source transactions.

11. If the result is still 0, inspect the criteria text. A visible Stock Out value may contain a trailing space. You can test a cell with:

    =LEN(B2)

    Compare the character count with a clean Stock Out label. The LEN function counts characters, including spaces.

12. Use filters to confirm that every Stock Out row has an item name. Any blank item cell indicates that the data structure still needs correction.

The key principle is simple: formatting should not replace data. Merged cells hide blank records, while formulas need explicit values in every row.

Implementation angle

This inventory layout is a good candidate for a structured data-entry process in Zoho Creator. Each movement can become a record with fields for item, movement type, quantity, date, and location, while Make or n8n can synchronize approved transactions back to Excel or a reporting system. An automation partner can also use Claude to inspect formulas and flag merged cells, blank item names, inconsistent Stock Out labels, and quantities stored as text before they affect reports.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

Why does SUMIFS return 0 for Stock Out in Excel?

The item name may be in a merged cell, so the Stock Out rows are blank in the underlying data. SUMIFS cannot match those rows to the item name. Unmerge the cells and enter the item name in every transaction row.

How do I sum Stock Out quantities by item in Excel?

Use SUMIFS with the quantity column as the sum range, then set criteria for the item and movement type. For example: =SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,"Stock Out"). Make sure every range covers the same rows and each Stock Out row has an item name.

What should I check if my SUMIFS formula still returns 0?

Check that the item name and movement type match the criteria exactly, and that quantities are stored as numbers. Look for blank item cells and extra spaces in labels such as “Stock Out.” A flat table with one complete transaction per row is easier to check and summarize.