+ Reply to Thread
Results 1 to 4 of 4

Google Sheets: How to combine CountIfs and an array formula

  1. #1
    Registered User
    Join Date
    05-17-2022
    Location
    Accra
    MS-Off Ver
    Excel version 16.61
    Posts
    2

    Question Google Sheets: How to combine CountIfs and an array formula

    Hi,

    This is my first time posting so I know you'll be nice

    I have created a sample spreadsheet (attached).

    Please note the real data is far larger with thousands of reviews and multiple features that have groups as big as 8.

    What I'm trying to do is to create formulas in the Summary tab where there are blank spaces.

    I am fairly new to array formulas but used them in column B to calculate the average review scores for the groups.

    I have countifs formulas for the single feature rows but don't know how to create a formula for each group row that will count the number of reviews that have at least one feature in that group where the review score is 3 or higher. I have all the grouped feature combinations in the Classes tab to the right. They don't include the negative options (00 or 0000). This helps me easily differentiate the review scores of those with at least one feature in a particular group and those which don't have any feature in that group (although this isn't included in the sample above)

    I hope that all makes sense when you look at the data.

    I know there will be a simple way to do it but it is beyond my Google Sheets level of brilliance!

    Can someone please help me?

    Thank you in advance

    Emily
    Attached Files Attached Files

  2. #2
    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: How to combine CountIfs and an array formula

    here is a Google Sheets version which will be better suited to create a solution:
    https://docs.google.com/spreadsheets...gid=2100307022
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    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: How to combine CountIfs and an array formula

    SOLUTION....

    on Summary sheet:

    into cell B2:
    Please Login or Register  to view this content.
    and drag down
    then change B6 to:
    Please Login or Register  to view this content.
    and change B9 to:
    Please Login or Register  to view this content.
    and change B12 to:
    Please Login or Register  to view this content.
    next, into cell C2:
    Please Login or Register  to view this content.
    and drag down

    then change C6 to:
    Please Login or Register  to view this content.
    and change C9 to:
    Please Login or Register  to view this content.
    and change C12 to:
    Please Login or Register  to view this content.
    lastly, into cell D2:
    Please Login or Register  to view this content.
    and drag down

    then change D6 to:
    Please Login or Register  to view this content.
    and change D9 to:
    Please Login or Register  to view this content.
    and change D12 to:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-17-2022
    Location
    Accra
    MS-Off Ver
    Excel version 16.61
    Posts
    2

    Re: Google Sheets: How to combine CountIfs and an array formula

    A bit late but I just remember that I never popped back to say thanks so much for your help. It worked perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. i cannot convert a Google sheets array formula to excel365
    By EzLedgers in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 09-20-2022, 04:59 AM
  2. Google Sheets: Array formula sumifs
    By Ritsuke in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 02-16-2022, 05:37 PM
  3. array formula for google sheets
    By cyrano3790 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 06-13-2021, 12:46 AM
  4. array formula for google sheets
    By cyrano3790 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2021, 12:46 AM
  5. Array Formula - Google Sheets -> Excel converter
    By dathgr in forum Excel General
    Replies: 4
    Last Post: 12-19-2018, 06:30 AM
  6. Array formula not working in Google Sheets
    By nappy1001 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 04-20-2018, 12:14 PM
  7. Google Sheets Array Formula Structure
    By redline24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2015, 04:00 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1