Jim has attended:
Excel Advanced course
Calculating counts and averages from a selection of data only
Hi,
I have a large spreadsheet of data listing weekly search engine rankings for a website for 450+ keywords.
I've already set up COUNTIFs for the number of keywords ranking in top 5, top 8 and top 10 respectively for each week: Eg. =COUNTIF(range,"<6")
I've also an average ranking per week.
However, a number of these keywords have been marked as "priority". Currently this is denoted by an X in an adjacent column (which can then be filtered).
I need to run the same COUNTIF and Average formulas again but only using these selected priority terms.
Is there a way to do both of these tasks without separating out the priority data from the rest of the database? Something like:
“IF a keyword in this range is a priority and in the Top 5 then count it?”
(For the number of top terms)
“Count all keywords in this range that are a priority and work out the average”
(For the average ranks of the priority keywords)
RE: Calculating counts and averages from a selection of data onl
Hi Jim
Thank you for your question. It looks like you might find Countifs
useful as it lets you use multiple criteria.
The syntax is similar to COUNTIF but you keep going, so:
=COUNTIFS(range1, condition1, range2, condition2,...)
All conditions must be TRUE before an item is counted.
Let me know if this works for you.
Kind regards,
Andrew
