SOLVED

# Sum of data across multiple columns & rows based on Criteria

Copper Contributor

# Sum of data across multiple columns & rows based on Criteria

I have a data set that ranges across multiple rows and columns.  I am building a nested formula and looking to sum QTY based on multiple criteria in one part of it.  I tried sumifs, however, that does not work.  Below is my example using a hypothetical sumifs (keeping in mind, this does not work).  Any suggestions on how to return my expected result would be greatly appreciated.

3 Replies
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: Sum of data across multiple columns & rows based on Criteria

Try

=SUMPRODUCT('PROD&DMD'!\$G\$4:\$AF\$2099, ('PROD&DMD'!\$A\$4:\$A\$2099=\$A4)*('PROD&DMD'!\$G\$2:\$AF\$2=\$M4)

# Re: Sum of data across multiple columns & rows based on Criteria

@HansVogelaar This worked perfectly! Thank you!

# Re: Sum of data across multiple columns & rows based on Criteria

OMG I didn't think this was possible! you have saved me hours of changing my data structure - THANK YOU!
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: Sum of data across multiple columns & rows based on Criteria

Try

=SUMPRODUCT('PROD&DMD'!\$G\$4:\$AF\$2099, ('PROD&DMD'!\$A\$4:\$A\$2099=\$A4)*('PROD&DMD'!\$G\$2:\$AF\$2=\$M4)