+ Reply to Thread
Results 1 to 8 of 8

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

  1. 02-24-2022, 03:19 AM #1
    Sleurgh
    Sleurgh is offline
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Lightbulb 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?

    Thanks in advance for your help!

    UNDECLARED CROSS POST: https://www.mrexcel.com/board/thread...rmula.1197206/
    Last edited by Glenn Kennedy; 02-24-2022 at 04:15 AM.
    Reply With QuoteRegister To Reply
  2. 02-24-2022, 04:14 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    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)
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 02-24-2022, 08:51 AM #3
    janmorris
    janmorris is offline
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    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.
    Reply With QuoteRegister To Reply
  4. 02-25-2022, 12:30 AM #4
    Sleurgh
    Sleurgh is offline
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

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

    Hi,

    Thank you for your reply.
    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 02-25-2022, 03:36 AM #5
    janmorris
    janmorris is offline
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

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

    formula in B14 should be:
    Please Login or Register  to view this content.
    and the formula in B24 can be:
    Please Login or Register  to view this content.
    then, because SUMPRODUCT does not like wildcards, we can force it with "--(ISNUMBER(SEARCH(...)))"

    thus making the formula in B18:
    Please Login or Register  to view this content.
    Last edited by janmorris; 02-25-2022 at 03:08 PM.
    Reply With QuoteRegister To Reply
  6. 02-25-2022, 08:04 AM #6
    Sleurgh
    Sleurgh is offline
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    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).
    Reply With QuoteRegister To Reply
  7. 02-25-2022, 10:25 AM #7
    janmorris
    janmorris is offline
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    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.

    if your question has been answered, please click the "Thread Tools" above your initial enquiry, and select [Solved]

    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.
    Reply With QuoteRegister To Reply
  8. 02-27-2022, 10:16 PM #8
    Sleurgh
    Sleurgh is offline
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    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!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Error Uploading to Google Drive with Sumproduct function (Sumif across multiple sheets)
    By Artie01 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 03-25-2021, 05:34 PM
  2. [SOLVED] Google Sheets: Totaling multiple cells TO a single cell on a different sheet
    By jgwalter in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-21-2021, 11:21 AM
  3. [SOLVED] Insert new line (within formula which is combining text) - GOOGLE SHEETS
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 05-12-2019, 05:55 PM
  4. Switching from Google sheets, need help with arrayformulas or combining range in formula
    By JurisBarro in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-11-2019, 06:38 AM
  5. used wildcards in formula sumproduct
    By Kilchomanfan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-11-2018, 12:58 PM
  6. [SOLVED] Complex SUMIFS (SUMPRODUCT?) formula w/ wildcards (SEARCH?)
    By markl41 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-11-2012, 02:24 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1