Why is creating a title the hardest part? Sum if same columns have "1" value on different sheet

[J.Ulmer]

Greetings - Self taught on the more advanced side of Excel (pivot tables, VBA, etc). Have learned a lot over the years from searching this site and others with Excel queries. So happy they exist! First time posting...

Trying to sum the range of actual values in columns B thru AE on the Data sheet based on whether or not a "1" is in the same ordered column fields on the Rev Field Index sheet for that unique CtyLocOut. Was thinking Index/Match would be the way to go, but can't figure out how to reference the actual values from the Data sheet when attempting to do the calculation on the Rev Field Index sheet. I've tried incorporating an Indirect formula, but was unable to figure out how to make it dynamic on the Rev Field Index sheet. Ultimately I want this sum on the Data sheet under RzRevCalc field.

Extrapolated data would have close to 100 CtyLocOut items and ballpark of 35,000+ rows on the Data sheet - if there are any size or slowness concerns. I'm not tied to the set up of the 1's and 0's for the Rev Field Index sheet if there is a better way to organize things.

Please let me know if I've left any other details out. Appreciate any help that can be provided!

--J

 

[John Jairo V]

Hi @J.Ulmer

If I understood, this can work:
=SUMIF(INDEX('Rev Field Index'!$B$2:$AE$17,MATCH(A2,'Rev Field Index'!$A$2:$A$17,),),1,B2:AE2)

Note: If the CityLocOut in Data Sheet always appear in Rev Field Index Sheet, and the column A in Rev Field Index Sheet is sorted ascending, then remove the comma (blue in formula) for increase performance.

Blessings!

 

[SirJB7]

Hi, J.Ulmer!

Welcome to Chandoo forums! Formally, since you seem to have been around for a while.
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, output in 3rd worksheet with format of 2nd one?

Regards!

<Edited: arrived late, John Jairo V writes faster than me >

 

[J.Ulmer]

Hi @J.Ulmer

If I understood, this can work:
=SUMIF(INDEX('Rev Field Index'!$B$2:$AE$17,MATCH(A2,'Rev Field Index'!$A$2:$A$17,),),1,B2:AE2)

Note: If the CityLocOut in Data Sheet always appear in Rev Field Index Sheet, and the column A in Rev Field Index Sheet is sorted ascending, then remove the comma (blue in formula) for increase performance.

Blessings!

Fantastic! Seems to work perfectly, I will test further with my larger workbook tomorrow. Thanks for your time and knowledge!