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.