Excel Arrays: average and if combining more than one criteria

I have a data for an assessment tool that has about 20 categories and about 200 items scored 0-3. The first half of the tool is mandatory, other sections are optional so there are a lot of blank areas. 4 items have the option of N/A.

I currently use arrays to gather average if data over multiple columns for each category. I got help on another forum and am including the formulas below as well. I use two different ones, as mentioned in some of the data ranges all items cells require a score, and for some sections, it is optional or not scored at all. In the sample grid below, I need to find the average for all the data columns combined (Q1-Q4) based one Assessment types B and N. Note: some scores are given option for n/a

``````T   Q1  Q2  Q3  Q4
N   0   0   3   2
B   1   2   3
N   1   1   0   0
N
D   0   n/a 2   3
O1  0   1   1   0
O1  2   0   0   0
O2  2   n/a 0   0
``````

The grid I pasted here doesn't seem to show correctly and I can't paste an image of it. Below it doesn't resemble my grid at all. So let's say that in column A I have different codes for assessment type. B, D, N, O1, O2. In columns B-E I have scores for each question. Some cells are blank for one whole row, a couple of rows in column C are marked N/A. So row two might be: N 0 1 3 0; row two might be B 0 N/a 2 2. Row three might be B (empty cells)

Current formulas for using one criteria:

``````=AVERAGE(IF(ANSAt!H:H="B",ANSAt!J:AC))
``````

and to not include blanks in sections that have lots of blanks

``````=AVERAGE(IF(ANSAt!\$H:\$H="B", IF(XMod!G:Y<>"", XMod!G:Y)))
``````

If anyone can help me show the example I would appreciate it :) I even tried to use the Snip tool.

If you can sort on the "T" column then you can use this:

``````=AGGREGATE(1,6,INDEX(\$B\$2:\$E\$9,MATCH(H2,\$A\$2:\$A\$9,0),1):INDEX(\$B\$2:\$E\$9,MATCH(H2,\$A\$2:\$A\$9,0)+COUNTIF(\$A\$2:\$A\$9,H2)-1,4))
``````

EDIT:

While you are trying to understand the above I thought I would add one more for you. This formula does not care if it is sorted or not:

``````=SUM(IF(ISERROR(INDEX((\$A\$2:\$A\$9=H2)*\$B\$2:\$E\$9,)),"",INDEX((\$A\$2:\$A\$9=H2)*\$B\$2:\$E\$9,)))/SUMPRODUCT(--(INDEX((\$A\$2:\$A\$9=H2)*ISNUMBER(\$B\$2:\$E\$9),)))
``````

This is an array formula. It needs to be confirmed with Ctrl-Shift-Enter when leaving edit mode. If done properly Excel will put `{}` around the formula.

Also it you want to hard code either of these formula with the lookup value directly replace the H2 reference with the desired String; `"N"`.

• That's pretty complicated! I haven't yet figured out where it says to average the data section if row A has B or N. Although you said to sort, so maybe that's where I'm missing something. I've asked a colleague to look at this too since she gets it more than me :) Apr 12, 2016 at 22:59
• @Avanell I put it in a small table in H and I. The formula is pasted in I and refers to H, If you want to hard code the lookup then change all the references of `H2` to `"N"` or `"B"` or whatever. there are three references that need to be changed. Apr 12, 2016 at 23:49
• We kind of get it...haven't tried out yet but will :) Apr 15, 2016 at 1:23
• @Avanell what are your concerns, about trying it? Also see the second formula I just put up. It works without sorting. Apr 17, 2016 at 21:17