# Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

1. ## Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

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 the number of page views),
- AVERAGEIF to calculate the average time
- 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?

2. ## Re: Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

Welcome (back) to the forum.

Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

1) if you have cross-posted, and

2) the URL of the cross posts.

That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

(Since you are new here, on THIS OCCASION I have done it for you)

3. ## Re: Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

according to the cross-post you believe the solution should work in both excel and google sheets.

if that is correct, can you please follow the instructions in the yellow banner (above your initial post) and upload an excel file with some sample data and highlight and add notes to clearly indicate where you are looking for the solution to be working.

4. ## Re: Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

Hi,

Really sorry if I got confused with the forum's guideline, I'm new here and I guess I did not pay enough attention to the indications.

I've attached a sample of the data with the formulas I've been trying in order to solve my problem.

To summarize, I'm trying to find the average session duration for the blog section of my website, depending on how many users visit each page.
I'm assuming combining AVERAGEIF, SUMPRODUCT and Wildcards is the way to solve my issue but I'm not sure how to put all of this together.
Hope it helps.

5. ## Re: Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

formula in B14 should be:
and the formula in B24 can be:
then, because SUMPRODUCT does not like wildcards, we can force it with "--(ISNUMBER(SEARCH(...)))"

thus making the formula in B18:

6. ## Re: Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

Hi Janmorris,

I guess my 2 initial formulas were overly-complicated and that's why I had issues aggregating everything together.
But it is working just fine right now thanks to you.
Thank you so much for your help, your time and your explanations, I really appreciate that !

(and again sorry for not paying enough attention to the forum's rules and for my lack of responsiveness).

7. ## Re: Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

no problem, im glad to help

being new environment can be difficult, and it can take some time to learn new rules that you are not used to.
even for me (after 6 months) i am still learning some of the rules so don't stress too much.

also, as you are new here, you may not be aware the forum has a system where you can thank all of the people who have helped you by clicking * Add Reputation under their name.

8. ## Re: Google Sheets: Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula

Yes, each forum is different and it takes some times to get used to the rules.

Anyways, thanks again for your precious help, I've added some reputation to you and changed the thread to "solved".

Wishing you a pleasant week!

There are currently 1 users browsing this thread. (0 members and 1 guests)