CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
wcndave
wcndave

Reputation: 314

How to Sum lots of IF results, where SUMIF and SUMIFS cannot be used

Sample Sheet

Have a sheet with items which have data attributes, and may be used for multiple purposes. There is a lookup table to lookup a score, based on the attributes.

So I can get the score for each item, see the top right section, and then sum that for each of the purposes. So purpose 1 has 11 data attribute points etc.

The score formula is included in the image for reference.

However, rather than copy all the data and score it, ideally would like a formula that can just go into a scoring column. Otherwise, with say 200 items, I need to have 201 columns just to score this one thing...

However, sumifs and sumif won't do this. What I really want is a "sum(foreach cell in range, do this formula)"

Does anyone know how this might be done?

Upvotes: 0

Views: 49

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

Just on this mini example, you could use

=ArrayFormula(sum(if(C4:E4="yes",vlookup(C$2:E$2,$B$9:$D$11,match(C$3:E$3,$C$8:$D$8,0)+1,false))))

so you do a lookup on attribute 2 to find which column to do the lookup on attribute 1.

enter image description here

Upvotes: 0

Related Questions