# Average based on 1 criteria

Options
edited 11/04/22

I'm sure this is user error but I'm trying to average out compensation based on the job code then I'd like to create either a data mesh or vlookup to fill in the blank compensation with these averages. I've tried having a seperate sheet that has the job codes and then I'm trying to use the averageif formula. However I keep getting Incorrect Argument set when i type in =AVERAGEIF({Comp}, {Job Code2}, [Job Code]@row)

Thoughts from anyone on how to do this?

• ✭✭✭✭✭✭
Options

I think you are usigne the wrong order of argument:

Try this. =AVERAGEIF({Job Code2}, [Job Code]@row,{Comp})

• ✭✭✭✭✭✭
Options

I think you are usigne the wrong order of argument:

Try this. =AVERAGEIF({Job Code2}, [Job Code]@row,{Comp})

• Options

@Christian G. that didn't work....all \$ are zero

• ✭✭✭✭✭✭
Options

Let's try another approch;

Collect all the Compasation for the jobcode at row

```collect({comp},{job code 2},=[job code]@row)
```

Then Average it

```=AVG(collect({comp},{job code 2},=[job code]@row))
```

• Options

@Christian G. This doesn't work, I'm getting an error of #INVALID COLUMN VALUE. I even tried something simple like countif to count how many times the job code is showing up and nothing is returning. The job code is in a format of '000682 to keep all the zeros at the front. Do you think that is making the difference?

• ✭✭✭✭✭✭
Options

Can you post screenshots of the sheets where the compensation and job code are, and where you try to get the formula?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!