sumif with offset?

[sonicslice]

HI folks -- I have a table with many columns; the columns contain survey items and related ratings. The order of columns is score/number/criteria/category, repeated up to 50 times. I'm pretty sure the right approach is to use sumif and offset to add the scores in specific categories, within each row. But I can't figure out how to nest the statement telling Excel to count the score in the column 2 to the left of each category. Any ideas?

 

[DRSteele]

If you could post some sample data we could help you more easily. Try the HTML Maker in the link below.

 

[sonicslice]

If you could post some sample data we could help you more easily. Try the HTML Maker in the link below.

Good point! Here's a sample of the data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]Score[/TD]
[TD]3[/TD]
[TD]Emotion[/TD]
[TD]Happy[/TD]
[TD]Time[/TD]
[TD]0:10[/TD]
[TD]Score[/TD]
[TD]4[/TD]
[TD]Emotion[/TD]
[TD]Excited[/TD]
[TD]Time[/TD]
[TD]0:23[/TD]
[TD]Score[/TD]
[TD]2[/TD]
[TD]Emotion[/TD]
[TD]Happy[/TD]
[TD]Time[/TD]
[TD]0:27[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

(Apologies, I can't get the HTMLmaker to run on my laptop so I've recreated a small sample here in this table.)

What I'm trying to do is create a function, say in column S, that scans the data in the previous columns and gives me an average score for each emotion. So "Happy", which appears in columns D and P, has the two scores 3 (column B) and 2 (column N). Meanwhile Excited in column J only has 1 score, in column H (which is a 4). So I'd like to identify and average the scores for Happy, in this case 2.5; then identify and average the scores for Excited, in this case 4; and so on for any other emotions.

In each case, the score is 2 columns to the left of the emotion that identifies it. So I figure using some combination of SUMIF and OFFSET this should be possible...but I'm too much of a n00b to create this function from scratch.

Thanks for your help!

 

[Jonmo1]

Try

=AVERAGEIF(D2:P2,"Happy",B2:N2)

 

[jasonb75]

Welcome to the board!

Simple to do, adjust as needed. Note that the second rage is offset from the first by 2 columns to match your data layout, you don't need to use the actual OFFSET function to achieve this.

=AVERAGEIF(D1:P1,"Happy",B1:N1)

 

[sonicslice]

Thanks Jonmo1 and jasonb75! You guys have saved me an immense amount of head scratching.

One further question: the rows on this table are different lengths (from 4-700+ columns). Creating this formula and including all of the columns is causing #DIV/0 errors, because many of the rows include blank cells. Is there a way to create this formula so that it ignores the blank cells?

 

[Jonmo1]

You can use iferror

=IFERROR(AVERAGEIF(D2:P2,"Happy",B2:N2),"")

 

[sonicslice]