Vertical and Horizontal Condition to Sum Array

[Aimee S.]

Hi Folks.

I cannot seem to figure out how to approach this one.

I am trying to sum integer values in array 'INSERT Tabbing Data-GIS'!$A$2:$BZ$5000

Where column header matches certain criteria, i.e. if 'INSERT Tabbing Data-GIS'!$A$1:$BZ$1 are all available columns, needs to match value in $C7 on native sheet.

And also where value is 1 in the column 'INSERT Tabbing Data-GIS'!$BQ$1:$BQ$5000

In other words, I want to sum all values in the array mentioned above where the column header matches $C7's value (I use this approach so I can drag down the formula in my native sheet to reference different part numbers) and only sum values where the value for the same row in column 'INSERT Tabbing Data-GIS'!$BQ$1:$BQ$5000 is 1

 

[DanteAmor]

Try this

=SUMIF('INSERT Tabbing Data-GIS'!$BQ$2:$BQ$5000,1,INDEX('INSERT Tabbing Data-GIS'!$A$2:$BZ$5000,0,MATCH($C7,'INSERT Tabbing Data-GIS'!$A$1:$BZ$1,0)))

 

[Aimee S.]

That works!!! I did not see your response in time but I also found this by pouring through various youtube vids and it also worked!!! =SUMIFS(INDEX('INSERT Tabbing Data-GIS'!$A$2:$BO$5000,,MATCH($C7,'INSERT Tabbing Data-GIS'!$A$1:$BO$1,0)),'INSERT Tabbing Data-GIS'!$BQ$2:$BQ$5000,1)

Thanks so much for making sense of my difficultly worded question. You rock.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.