COUNTIF AVERAGEIF

A Rose

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! 😃

Antonio Figueroa

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)

Antonio Figueroa

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

A Rose

@Antonio Figueroa ,

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!

Antonio Figueroa

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

A Rose

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!

Antonio Figueroa

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)