Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

[Olala]

Hi,

I have a list of URLs from my company website.
I'm trying to calculate the average time spent on-page for one section of my website /company/blog/... (and all the pages that contains this URLs)
I'm assuming I need a combination of:
- SUMPRODUCT (to calculate the weighted time on-page based on page views),
- AVERAGEIF to calculate the average time spent on-page for the section
- Wildcards to include URLs that contains "/company/blog"

So far I have those two formula that works well individually, but I cannot find a way to combine them:

AVERAGEif($A$2:$A$500,"*.com/blog*",$C$2:$C$500)
SUMPRODUCT($C$2:$C$500,$B$2:$B$500)/$B$501

Is there any way to combine everything together? Do I need extra formulas?

Thanks in advance for your help!

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula [SOLVED]

Hi, I have a list of URLs from my company website. I'm trying to calculate the average time spent on-page for one section of my website /company/blog/... (and all the pages that contains this URLs) I'm assuming I need a combination of: - SUMPRODUCT (to calculate the weighted time on-page...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Olala]

Here is the link of the other post:

Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula [SOLVED]

Hi, I have a list of URLs from my company website. I'm trying to calculate the average time spent on-page for one section of my website /company/blog/... (and all the pages that contains this URLs) I'm assuming I need a combination of: - SUMPRODUCT (to calculate the weighted time on-page...

www.excelforum.com

 

[Peter_SSs]

From that cross-post link it appears that yours is a Google Sheets question. Can you confirm that is the case?
If so, since you have posted in the Excel Questions forum, we will move your thread to the appropriate forum:

 

[Olala]

Hi Peter,

I use both Excel and Google Sheet.
Sorry I'm still not very familiar with the different sections of this forum.
I believe all the needed formulas will be available in Excel and Google Sheets as well.

 

[Peter_SSs]

OK, thanks. In that case I will leave it here.

 

[Olala]

I've just got the solution here in my cross-post:

Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula [SOLVED]

Hi, I have a list of URLs from my company website. I'm trying to calculate the average time spent on-page for one section of my website /company/blog/... (and all the pages that contains this URLs) I'm assuming I need a combination of: - SUMPRODUCT (to calculate the weighted time on-page...

www.excelforum.com

 

[Peter_SSs]

Thanks for letting us know.