r/excel•
OverlordQ

Sum specific cells from a column based on criteria from another sheet

unsolved

I have three sheets. ScoreInputs, RawScores and ScoreMatrix.

ScoreInputs is basically a single column of raw input scores.

In ScoreMatrix, the first row is a list of score categories, then going down, a non-empty value if that question applies to that category.

In RawScoreCalc I'm trying to do the proper summation of the values from ScoreInputs based on ScoreMatrix.

I've started with

=MATCH(INDIRECT(ADDRESS(ROW() - 1, COLUMN() ) ), ScoreMatrix!$B1:$Z1) 

which will tell me which is the proper column to query from ScoreMatrix, and then

=COUNTIF(INDIRECT(ADDRESS(2,MATCH(INDIRECT(ADDRESS(ROW() - 1, COLUMN() ) ), ScoreMatrix!$B1:$Z1))&":"&ADDRESS(200,MATCH(INDIRECT(ADDRESS(ROW() - 1, COLUMN() ) ), ScoreMatrix!$B1:$Z1))),"<>") 

will tell me the number of non-zero rows, but I'm unsure how to parlay that into summing the matching rows from ScoreInputs.

So for example based on the ScoreMatrix, on RawScoreCalc LP1 should have the sum of cells ScoreInput!B6, ScoreInput!B8, ScoreInput!B10, etc. Which right now, I just did a static selection of the proper rows.

Posted Feb 10, 2024, 12:20 AM UTC

27

Comments aren't archived yet

This post had 7 comments on Reddit. Comments for this period haven't been loaded into the archive yet.