SUM ARRAY Formula Issue - Probably an easy one...

In my raw data I have two single-column OFFSET Named Ranges; [PlantID]; [SqFootage].

In my output sheet, I am attempting to SUM square footage p/PlantID. There are several rows in the raw data table that may have the same PlantID. The issue is that some of the PlantID's are whole integers (e.g. 300), while some PlantID's are alpha/numeric strings (e.g. 1A1). Currently I am receiving output ONLY for the PlantID's that contain both number AND text (e.g. 1A1), however, I get no results for the whole integer PlantID strings...

I have validated my OFFSET Named Ranges and they are working as intended. Here is my SUM ARRAY formula:

{=SUM(IF((PlantID = D3), SqFootage))}

*Does anyone know why my results are only outputting for alpha/numeric strings in the [PlantID] column, yet discarding the whole integer PlantID's?

we'd have to see your dataset to be certain, but i'd guess either D3 or the "integer" PlantID's are actually text... make sure the formatting between those two items is exactly the same (add a ' before the number to make it text)

it is because numbers are not a string... my programming knowledge is fairly limited, so i can't quite tell you exactly why this is the case, but basically every data management software with which i've interacted gets rather upset if you try to combine data types (i.e. strings and integers). this is one of the most common issues i see with people trying to use vlookup() or match() - they are looking for a string in a series of integers or vice-versa. i also run into it fairly regularly when trying to write vba - not knowing which data type is going to be returned by a specific formula can be problematic.

My preferred solution is to ensure consistency in data types across the board - always ensure that numbers and strings do not mix, especially within the same column. sometimes this means working with the person who provides your input(s) to ensure they provide data in a format which is appropriate for your needs.

anyway, glad i could help, don't forget to mark the post as solved!

Try using a regular SUMIF function - you don't need "array entry" and SUMIF is "agnostic" when it comes to data type in the criteria, I.e it will match text values with numbers and vice versa as long as the actual content is the same

=SUMIF(PlantID,D3,SqFootage)