+ Reply to Thread
Results 1 to 4 of 4

Converting Unique,Filter,Countif formula in Google Sheets to Excel

  1. #1
    Registered User
    Join Date
    03-31-2019
    Location
    Virginia
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Converting Unique,Filter,Countif formula in Google Sheets to Excel

    Hi,
    I have a list of students (by ID#) with Ds,D+, and Fs. I need a formula to organize them into 3 categories: Only 1 D or D+, >1D or D+, 1 or more Fs. Students can only be in one of the categories and I need their student ID returned.
    Since I work mostly in Google Sheets, I was able to get the right formulas but for security reasons I need to move it over to Excel. And I just realized Excel and Sheets formulas are not all from the same world.

    Any help would be much appreciated!

    attaching a sample
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,492

    Re: Converting Unique,Filter,Countif formula in Google Sheets to Excel

    Please try at
    C2 and Press Ctrl+Shift+Enter
    =IFERROR(SMALL(IF(FREQUENCY(IF(LEFT($B$2:$B$11)="D",$A$2:$A$11),$A$2:$A$11)=1,$A$2:$A$11),ROWS(C$2:C2)),"")

    D2 and Press Ctrl+Shift+Enter
    =IFERROR(SMALL(IF(FREQUENCY(IF(LEFT($B$2:$B$11)="D",$A$2:$A$11),$A$2:$A$11)>1,$A$2:$A$11),ROWS(C$2:C2)),"")

    E2 and Press Ctrl+Shift+Enter
    =IFERROR(SMALL(IF(FREQUENCY(IF(LEFT($B$2:$B$11)="F",$A$2:$A$11),$A$2:$A$11)>0,$A$2:$A$11),ROWS(C$2:C2)),"")

    Drag C2:E2 Down
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-31-2019
    Location
    Virginia
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Re: Converting Unique,Filter,Countif formula in Google Sheets to Excel

    Thanks, those formulas do seem to work perfectly. Question though: when I tried to adjust the formula to include more rows, it stopped working and just gave me a blank (even after I used ctrl+shift+enter). I'm going to use this spreadsheet as a template for a few teachers to enter data of unknown varying sizes. I want the formula to work say to row 1000 to cover all possibilities. How can I do that?

    For instance in C2 I tried:
    =IFERROR(SMALL(IF(FREQUENCY(IF(LEFT($B$2:$B$100)="D",$A$2:$A$100),$A$2:$A$100)=1,$A$2:$A$100),ROWS(C$2:C2)),"")

    Which just changed all row 11's to row 100, but that didn't seem to work.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,492

    Re: Converting Unique,Filter,Countif formula in Google Sheets to Excel

    Please try this and press Ctrl+Shift+Enter

    =IFERROR(SMALL(IFERROR(IF(FREQUENCY(IF(LEFT($B$2:$B$100)="D",$A$2:$A$100),$A$2:$A$100)=1,$A$2:$A$100),""),ROWS(C$2:C2)),"")

+ 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. Converting index arrayformula from Google Sheets to Excel
    By Kibson in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-03-2018, 10:07 AM
  2. [SOLVED] Converting Google Sheets QUERY to Correct Excel Formula
    By davis909 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2017, 09:14 AM
  3. Excel equivalent to Google Sheets' FILTER(range, condition)?
    By Trille in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-14-2016, 06:09 PM
  4. Converting google sheets formula to excel
    By coldeskimo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2016, 08:41 AM
  5. [SOLVED] Converting Google Sheets document with query to Excel
    By playmate in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-04-2015, 10:18 PM
  6. google filter formula in excel?
    By shinkirou in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2013, 09:19 AM
  7. Google Filter to Excel Formula needed
    By gmckinlay in forum Excel General
    Replies: 1
    Last Post: 01-17-2012, 09:12 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