# COUNTIF AVERAGEIF

Options
✭✭✭✭✭

Hi,

I’m trying to create a formula, that includes all that the Column Status contains “Current” & Column Rep. Name contains “Mike Bass” average on Column “Total Spent

I tried the below but didn’t work.

=COUNTIF(Status:Status, ="Current", (AVERAGEIF([Rep. Name]:[Rep. Name], ="Mike Bass", [Total Spent]:[Total Spent])))

Pls help out.

Thank you! 😃

• ✭✭✭✭✭✭
Options

Try This

Formula Below Goes in Average Column

=SUMIF([Rep Name]1:[Rep Name]5, [Rep Lookup]@row, Total1:Total5) / COUNTIF([Rep Name]1:[Rep Name]5, [Rep Lookup]@row)

• ✭✭✭✭✭✭
Options

I only have it working for 5 rows. You would need to change the formula to do more rows.

• ✭✭✭✭✭
Options

Thanks for your help!

I tried the below for the entire column,

just 1 question, what in the formula would be the "Current" column?

=SUMIF([Rep Name]:[Rep Name], [Rep Lookup]@row, Total:Total) / COUNTIF([Rep Name]:[Rep Name], [Rep Lookup]@row)

Thank you!

• ✭✭✭✭✭✭
Options

I don't understand your question. How are you determining if it is current or not?

• ✭✭✭✭✭
edited 03/02/21
Options

hi,

My question is, I want the formula to include only those that "Current" column, contains "Current"

I don't think your formua calculates that as well, right?

Thank you!

• ✭✭✭✭✭✭
Options

Okay I got it but you will have to make 2 helper column

In column8 =[Rep Name]@row + Current@row

Column 9 =[Rep Lookup]@row + "Current"

Average =SUMIF([Column8]1:[Column8]5, [Column9]@row, Total1:Total5) / COUNTIF([Column8]1:[Column8]5, [Column9]@row)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!